Generating Combinations in Power Query
In this post I wanted to share the function I have created to generate combinations in Power Query. The function will accept two parameters. The first parameter is a list you want to use to generate the combinations, the second parameter is a number representing the number of items for each combination.
In terms of speed there is probably room for improvement as the function may take a few minutes for very large combinations. I will probably revisit it again in the future but for the moment it gives me the results I wanted. Also it would be good if someone can suggest how it could be sped up for large combinations.
The function:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 |
(Source as list, SetNumber as number) as table => let // number of combinations NumberOfCombinations = Number.Combinations(List.Count(Source), SetNumber), /* create SetNumber - 1 Lists, skipping one progressive items from Source For a "A B C D E" list for which we want to generate combinations of 3, return 3 lists of: A B C D E B C D E C D E */ PartitionList = List.Buffer(List.Accumulate( {0..SetNumber - 1}, {}, (state, current) => state & {List.Skip(Source, current)} )), // item count of the last item of PartitionList LastListLength = List.Count(List.Last(PartitionList)), // generate list of 1's of length equal to setNumber FirstListItems = List.Repeat({1}, SetNumber), // set number items positions, SetNumberItemsPosition = List.Buffer({0..SetNumber - 1}), // for each item in Sequence list, take the correspondend item of Partition list AssignItems = (Sequence as list) as list => List.Accumulate( SetNumberItemsPosition, {}, (state, current) => state & {PartitionList{current}{Sequence{current}-1}} ), GenerateNewNumList = (PrevNumList as list) => List.Accumulate( SetNumberItemsPosition, {}, (state, current) => let Res = if current = SetNumber - 1 then if PrevNumList{current} = LastListLength then state{current - 1} else PrevNumList{current} + 1 else if PrevNumList{current + 1} = LastListLength and PrevNumList{current} < LastListLength then PrevNumList{current} + 1 else if PrevNumList{current} = LastListLength then state{current - 1} else PrevNumList{current} in state & {Res} ), Result = List.Generate( ()=>[NumList = FirstListItems, Combin = AssignItems(NumList), counter = 1], each [counter] <= NumberOfCombinations, each [NumList = GenerateNewNumList([NumList]), Combin = AssignItems(NumList), counter = [counter] + 1], each [Combin] ), FinalTable = Table.FromRows(Result) in FinalTable |
Calling the function:
I will show below how to call the function and the preview of the results you would get
|
1 2 3 4 5 6 |
let Source = {"Red","Green","Yellow","Brown","Black","Purple","Pink","Blue","Orange"} in fnGenerateCombinations(Source,4) |
Combinations in Power Query
|
1 2 3 4 5 6 |
let Source = {"Red","Green","Yellow","Brown","Black","Purple","Pink","Blue","Orange"} in fnGenerateCombinations(Source,3) |
Combinations in Power Query
Of course you are not limited to a list containing text, it could very well be a list of number, or a list of records, or a list of tables or a list of lists.
Livio
Very informative article, great work
thanks for sharing, Keep up the good work!
Thank you Shobi! Your feedback is very much appreciated.
It would be very helpful if you could make a tutorial..