Combinatorics in Power Query -part1

[smpa01]

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.