Forum Discussion
Merging Reporting Periods Into a Calendar Table
In Power Query.
Assuming your reporting period table looks like...
add a column that creates a list of dates from the start date to and including the end date.
List.Dates([Start], Number.From([End]-[Start])+1, #duration(1,0,0,0))remove the Start and End columns
expand the created column to new rows.
You should now have a table with two columns, Reporting Period and the coresponding dates.Merge the reporting period query into the date query using the date columns as the keys.
Expand only the Reporting Period column from the resulting merge.
Fill up so any dates prior to the first reporting period are 'Period 1'
And the result should be what you are looking for.
In Power Query.
Assuming your reporting period table looks like...
add a column that creates a list of dates from the start date to and including the end date.
List.Dates([Start], Number.From([End]-[Start])+1, #duration(1,0,0,0))remove the Start and End columns
expand the created column to new rows.
You should now have a table with two columns, Reporting Period and the coresponding dates.
Merge the reporting period query into the date query using the date columns as the keys.
Expand only the Reporting Period column from the resulting merge.
Fill up so any dates prior to the first reporting period are 'Period 1'
And the result should be what you are looking for.
