Add a variable number of columns automatically, based on parameters

[nickvanmaele]

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. 

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):

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. 

[nickvanmaele]

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 · Accepted solution]

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

Then expand this list column.