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.