- Power BI forums
- Get Help with Power BI
- Desktop
- Service
- Report Server
- Power Query
- Mobile Apps
- Developer
- DAX Commands and Tips
- Custom Visuals Development Discussion
- Health and Life Sciences
- Power BI Spanish forums
- Translated Spanish Desktop
- Training and Consulting
- Instructor Led Training
- Dashboard in a Day for Women, by Women
- Galleries
- Community Connections & How-To Videos
- COVID-19 Data Stories Gallery
- Themes Gallery
- Data Stories Gallery
- R Script Showcase
- Webinars and Video Gallery
- Quick Measures Gallery
- 2021 MSBizAppsSummit Gallery
- 2020 MSBizAppsSummit Gallery
- 2019 MSBizAppsSummit Gallery
- Events
- Ideas
- Custom Visuals Ideas
- Issues
- Issues
- Events
- Upcoming Events
- Community Blog
- Power BI Community Blog
- Power BI 中文博客
- Community Support
- Community Accounts & Registration
- Using the Community
- Community Feedback

Turn on suggestions

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now

- Power BI forums
- Forums
- Get Help with Power BI
- Power Query
- Re: How to dynamically/recursively add columns to ...

Topic Options

- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

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

05-25-2023
06:38 AM

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.

1 ACCEPTED SOLUTION

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

05-25-2023
06:56 AM

@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

Latest book!:

DAX is easy, CALCULATE makes DAX hard...

2 REPLIES 2

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

05-25-2023
06:56 AM

@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

Latest book!:

DAX is easy, CALCULATE makes DAX hard...

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

05-26-2023
12:20 AM

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.

Announcements

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

Learn from experts, get hands-on experience, and win awesome prizes.

Top Kudoed Authors

User | Count |
---|---|

67 | |

61 | |

23 | |

17 | |

13 |