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"}),
#"Added Custom" = Table.AddColumn(
#"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",
z = Text.PadStart(y, List.Count(Source), "0")
in
z
),
#"Added Custom1" = Table.AddColumn(#"Added Custom", "Custom.1", each Text.ToList([Custom])),
#"Added Custom2" = Table.AddColumn(
#"Added Custom1",
"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)
),
#"Added Custom3" = Table.AddColumn(
#"Added Custom2",
"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
),
Combinations = #"Added Custom3"[Combinations]
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.

## Be the first to comment