The ultimate Microsoft Fabric, Power BI, Azure AI, and SQL learning event: Join us in Stockholm, September 24-27, 2024.

**Save €200 with code MSCUST on top of early bird pricing!**

- Power BI forums
- Updates
- News & Announcements
- 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
- Custom Visuals Community Blog
- 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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

- 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.