Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Get certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now

Reply
RZ
Frequent Visitor

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

 

 

 

 

1 ACCEPTED SOLUTION
Greg_Deckler
Super User
Super User

@RZ You might find this helpful, it's an example of recursive code in Power Query: Fun with Graphing in Power BI - Part 5-by-5 - Microsoft Fabric Community



Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Power BI Cookbook Third Edition (Color)

DAX is easy, CALCULATE makes DAX hard...

View solution in original post

2 REPLIES 2
Greg_Deckler
Super User
Super User

@RZ You might find this helpful, it's an example of recursive code in Power Query: Fun with Graphing in Power BI - Part 5-by-5 - Microsoft Fabric Community



Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Power BI Cookbook Third Edition (Color)

DAX is easy, CALCULATE makes DAX hard...

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. 

Helpful resources

Announcements
November Carousel

Fabric Community Update - November 2024

Find out what's new and trending in the Fabric Community.

Live Sessions with Fabric DB

Be one of the first to start using Fabric Databases

Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.

Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.

Nov PBI Update Carousel

Power BI Monthly Update - November 2024

Check out the November 2024 Power BI update to learn about new features.