Forum Discussion

RZ's avatar
RZ
Frequent Visitor
3 years ago
Solved

How to dynamically/recursively add columns to generate permutations..

Hello,

 

I am working on generating permutations for a specific applications. Basically the problem is there are N steps (24 as of now but can change) and at each step there is a decision to be made. And based on this decision I will be making a calculation.  And the maximum number of decisions that can be made is also a parameter. (k) 

 

So for N = 4, k=1 the possibilities are;

 

0000
0001
0010
0100
1000

 

 

And for N=4, k=2 the possibilities are; so k=n includes k=n-1...

 

0000
0001
0010
0100
1000
0011
0101
0110
1001
1010
1100

 

 

I thought (not verified) the fastest way to generate the k=1 values is to generate a list of values 2^0...2^N and convert them to binary if I need a visual representation.

 

 

decisionslist1 = = Table.FromList(List.Generate(()=>[x = 0, y=0],each [x] <= N, each [x=[x]+1,y=Number.Power(2,x-1)],each [y]), Splitter.SplitByNothing(), null, null, ExtraValues.Error)
decimal - binary
0        0000
1        0001
2        0010
4        0100
8        1000

 

 

And then for k=2 (and potentiallyfor all the next k) a sensible approach seemed to cartesian product the previous list with itself add the values and eliminate duplicates.

 

I could write the code for k=2 but I can't figure out a way to make it generic, which I think requires dynamically adding columns or recursion.

 

let
    N = 4,
    decisionslist1 = Table.FromList(List.Generate(()=>[x = 0, y=0],each [x] <= N, each [x=[x]+1,y=Number.Power(2,x-1)],each [y]), Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    cartesian = Table.AddColumn(decisionslist1, "Custom", each decisionslist1),
    expanded = Table.ExpandTableColumn(cartesian, "Custom", {"Column1"}, {"Column1.1"}),
    renamed = Table.RenameColumns(expanded,{{"Column1", "a"}, {"Column1.1", "b"}}),
    select = Table.SelectRows(renamed,each ([a] <> [b]) or (Number.From([a]) = 0 and Number.From([b])=0)),
    sum = Table.AddColumn(select, "Custom", each [a]+[b]),
    removeDuplicates = Table.Distinct(sum, {"Custom"}),
    decisionlist2 = Table.RemoveColumns(removeDuplicates,{"a", "b"})
in
    decisionlist2

outputing 

 

decimal - binary
0         0000
1         0001
2         0010
4         0100
8         1000
3         0011
5         0101
9         1001
6         0110
10        1010
12        1100

How can I convert this into a generic function does this k-1 times taking k as an input? 

 

I understand power query is not the ideal environment for such work but I am unfortunately there...

 

Thank you

Ruchan Ziya

 

 

 

 

2 Replies

    • RZ's avatar
      RZ
      Frequent Visitor

      Thanks,

       

      Not very elegant but I managed to get it working by passing previousState and iteration count down the iteration thanks to your push to the right direction.

       

      (N as number, k as number, optional prevStep as any, optional iteration as number) as any =>
      let
          decisionslist1 = Table.FromList(List.Generate(()=>[x = 0, y=0],each [x] <= N, each [x=[x]+1,y=Number.Power(2,x-1)],each [y]), Splitter.SplitByNothing(), null, null, ExtraValues.Error),
          iteration = if iteration is null then 1 else iteration,
          prevStep = if prevStep = null then decisionslist1 else prevStep,
          cartesian = Table.AddColumn(decisionslist1, "Custom", each prevStep),
          expanded = Table.ExpandTableColumn(cartesian, "Custom", {"Column1"}, {"Column1.1"}),
          renamed = Table.RenameColumns(expanded,{{"Column1", "a"}, {"Column1.1", "b"}}),
          select = Table.SelectRows(renamed,each ([a] <> [b]) or (Number.From([a]) = 0 and Number.From([b])=0)),
          sum = Table.AddColumn(select, "Column1", each [a]+[b]),
          removeDuplicates = Table.Distinct(sum, {"Column1"}),
          removedcolumns = Table.RemoveColumns(removeDuplicates,{"a", "b"}),
          decisionlist2 = if iteration = k then prevStep else GetPermutations(N,k,removedcolumns, iteration+1),
          out = decisionlist2
      in
          out

       

      coupling this with the toBinary function from Solved: Re: Number to Binary - Microsoft Fabric Community I am able to create the binary numbers(states/decisions for my purpose) up to k set bits at an acceptable performance... 

       

      for N=8, k=2...

      Column1	toBinary
      0	000000000000000000000000
      1	000000000000000000000001
      2	000000000000000000000010
      3	000000000000000000000011
      4	000000000000000000000100
      5	000000000000000000000101
      6	000000000000000000000110
      8	000000000000000000001000
      9	000000000000000000001001
      10	000000000000000000001010
      12	000000000000000000001100
      16	000000000000000000010000
      17	000000000000000000010001
      18	000000000000000000010010
      20	000000000000000000010100
      24	000000000000000000011000
      32	000000000000000000100000
      33	000000000000000000100001
      34	000000000000000000100010
      36	000000000000000000100100
      40	000000000000000000101000
      48	000000000000000000110000

       

       I would have shared a more generic function if I could, will do maybe one day if I can.