Power BI is turning 10, and we’re marking the occasion with a special community challenge. Use your creativity to tell a story, uncover trends, or highlight something unexpected.
Get startedJoin us for an expert-led overview of the tools and concepts you'll need to become a Certified Power BI Data Analyst and pass exam PL-300. 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.
This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.
Check out the June 2025 Power BI update to learn about new features.
User | Count |
---|---|
14 | |
13 | |
8 | |
8 | |
7 |
User | Count |
---|---|
17 | |
13 | |
7 | |
6 | |
6 |