Check your eligibility for this 50% exam voucher offer and join us for free live learning sessions to get prepared for Exam DP-700.
Get StartedDon't miss out! 2025 Microsoft Fabric Community Conference, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount. Prices go up February 11th. Register now.
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:
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.
Solved! Go to 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.
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.
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.
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Prices go up Feb. 11th.
Check out the January 2025 Power BI update to learn about new features in Reporting, Modeling, and Data Connectivity.
User | Count |
---|---|
14 | |
13 | |
12 | |
12 | |
12 |