This query involved taking the data and splitting it into four partitions using power query.
I’ve split the data into four partitions and the query folds which means that a Power Query query generates a single query statement to retrieve and transform source data.
The Power Query mashup engine strives to achieve query folding whenever possible for reasons of efficiency.
I then used the custom list functions which are fabulous way to create a distinct list of values and then count those values. I could then use these as variables to feed into my power query code to give to table partition.
Rather pleased with myself. Also discovered Paul Turley‘s post on SQL or M which looks interesting. https://lnkd.in/dxUecPe
I guess from a business perspective you could define the split depending upon customer requirement which could then ease the loading / refresh of data you already have in the model.
More reading I think.