Starting December 3, join live sessions with database experts and the Microsoft product team to learn just how easy it is to get started
Learn moreGet 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
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
Solved! Go to Solution.
@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
@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
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.
Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.
User | Count |
---|---|
29 | |
17 | |
11 | |
9 | |
8 |
User | Count |
---|---|
42 | |
24 | |
21 | |
13 | |
11 |