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

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. 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


@ 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!:
Mastering Power BI 2nd Edition

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


@ 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!:
Mastering Power BI 2nd Edition

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
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

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

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.

Top Solution Authors
Top Kudoed Authors