cancel
Showing results for
Did you mean:

## Combinatorics in Power Query -part1

Combinatorics is one of the most important areas of data analysis. It helps in painting a meaningful picture from tons of data very quickly.

We are often faced with a situation when we need to quickly detemine which combination of factors are actually driving the subtotal. In order to know that, it is important to generate all the subsets of a set and perform aggregation by those combinations in order to answer that question.

It is possible to generate all possible combinations of items in PowerBI using Power query and this post focuses on how to do that.

To elaborate, let's suppose there is a list as following

``````let
Source = {"powerBI","powerQuery","DAX"}
in
Source``````

and the end goal is to generate all possible subsets of this set which would be following

``````{},

{"powerBI"},{"powerQuery"},{"DAX"},

{"powerBI","powerQuery"},{"powerQuery","DAX"},{"powerBI","DAX"},

{"powerBI","powerQuery","DAX"}
``````

The following code generates all possible subsets of this set.

``````let
Source = {"powerBI","powerQuery","DAX"}
p1 = List.Transform({1 .. Number.Power(2, List.Count(Source))}, each _ - 1),
#"Converted to Table" = Table.FromList(p1, Splitter.SplitByNothing(), {"Value"}),
#"Converted to Table",
"Custom",
each
let
Loop = List.Generate(
() =>
[i = [Value], j = Number.IntegerDivide(i, 2), k = Number.Mod(i, 2), l = Text.From(k)],
each [i] > 0,
each [
i = [j],
j = Number.IntegerDivide(i, 2),
k = Number.Mod(i, 2),
l = Text.From(k) & [l]
],
each [l]
),
y = try Loop{List.Count(Loop) - 1} otherwise "0",
in
z
),
"Custom.2",
each
let
x = [Custom.1],
Terminate = List.Count(x),
Loop = List.Generate(
() => [i = 0, j = x{i}, k = if j = "1" then Source{i} else null],
each [i] < Terminate,
each [i = [i] + 1, j = x{i}, k = if j = "1" then Source{i} else null],
each [k]
)
in
List.RemoveNulls(Loop)
),
"Combinations",
each
let
x = [Custom.2],
y = List.Generate(
() => [i = 0, j = x{i}, k = j],
each [i] < List.Count(x),
each [i = [i] + 1, j = x{i}, k = [k] & "," & j],
each [k]
)
in
try y{List.Count(y) - 1} otherwise null
),
in
Combinations``````

The code starts from here

and generates this

An optimized version of the above code is following

``````let
Source = {"powerBI","powerQuery","DAX"},
Initiator={{}},
Loop = List.Generate(
()=>[i=0,j=Source{i},k=List.Combine({Initiator{i},{j}}),l=List.InsertRange(Initiator,List.Count(Initiator),{k})],
each[i]<List.Count(Source),
each[i=[i]+1,j=Source{i},k=[l],l=
let x = List.Generate(
()=>[a=0,b=k{a},c=List.Combine({b,{j}}),d=List.Combine({k,{c}})],
each [a]<List.Count(k),
each [a=[a]+1,b=k{a},c=List.Combine({b,{j}}),d=List.Combine({[d],{c}})],
each[d]  ) in x{List.Count(x)-1}],
each [l]

)
in
List.Transform(Loop{List.Count(Loop)-1},each Text.Combine(_,","))``````

A parameterized version with the choice a different delimiter is avaialble here

In my next post, I will show how this concept applies to a real-life scenario.