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

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

Reply
nickvanmaele
Advocate II
Advocate II

Add a variable number of columns automatically, based on parameters

Hi, 

My question is how I can add a variable number of date columns to an existing table in an automatic way. 

 

Context

I am trying to create a large fact table filled with test data by using only Power Query functions.

To ilustrate the approach, I will use a small table as an example here. 

 

My final fact table will have 4 columns:

  • 3 dimension columns: "System ID", "Threshold Level", and "Date"
  • 1 fact column: "Value", filled with an arbitrary number (the value of that number does not matter, it's just for volume testing)

 

The "System ID" column and "Threshold Level" are generated by randomly choosing an ID from a list of possible IDs. This part of the data generation is already OK. 

 

The "Date" column should have every date in the interval determined by parameters pStartDate and pEndDate. If I first create a table with two columns, "System ID" and "Threshold Level", each key combination [System ID, Threshold Level], i.e., each row in that two-column table, should appear for each date in the interval [pStartDate, pEndDate].

 

So, for example, in the screenshot below, there are three days in interval [pStartDate, pEndDate], and 10 rows of [System ID, Threshold Level] combinations in the original table, so I would like to obtain 10*3=30 rows, where each single combination of [System ID, Threshold Level] should exist for every date in the date interval. 

Table before adding date columns.png

 

 

I think the simplest way would be to add all dates from pStartDate and pEndDate as columns, and then unpivot the date columns. So, just prior to unpivoting the dates, I am looking to create the table below (with the value 10 just being any arbitrary number):

Table after adding date columns.png

 

Question

My question is: how can I add the date columns recursively for each date in interval [pStartDate, pEndDate]? 

In other words, how can I recursively call Table.AddColumn( ) as many times are there are dates? 

 

Thanks in advance for pointing me in the right direction. 

If there is an approach other than unpivoting, feel free to suggest it. 

 

1 ACCEPTED SOLUTION
AlexisOlson
Super User
Super User

This is easier to do directly in an unpivoted format (you can pivot afterward if you feel like it).

 

Add a custom column that is a list of dates from pStartDate to pEndDate.

List.Dates(pStartDate, Duration.Days(pEndDate - pStartDate)+1, #duration(1,0,0,0))

AlexisOlson_0-1673644741985.png

 

Then expand this list column.

AlexisOlson_1-1673644830414.png

View solution in original post

2 REPLIES 2
nickvanmaele
Advocate II
Advocate II

Hi @AlexisOlson 

Thank you so much for that elegant solution. Better than the strategy that I was following. 

 

Note: for some strange reason, the "Accept this as solution" button is not visible on your reply. It was visible, but when I pressed it, my own post got marked as the solution, instead of your reply. Some bug, no doubt. You certainly deserve your reply to be marked as solution. I will see if I can still get the site to do this. 

AlexisOlson
Super User
Super User

This is easier to do directly in an unpivoted format (you can pivot afterward if you feel like it).

 

Add a custom column that is a list of dates from pStartDate to pEndDate.

List.Dates(pStartDate, Duration.Days(pEndDate - pStartDate)+1, #duration(1,0,0,0))

AlexisOlson_0-1673644741985.png

 

Then expand this list column.

AlexisOlson_1-1673644830414.png

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

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

Top Solution Authors
Top Kudoed Authors