How to unpivot date range in Excel/ PowerQuery

Question

Would someone be able to tell me the best way of unpivoting the date range that currently appears in each row: start date and end date, so that the 'Title' appears row by row for each date included in the date range.

I expect the title not to appear once, but line by line as many times as the date range indicates - so if the date range runs from 01/12/2022 to 03/12/2022 the Title will appear three times with the calendar date in the column next to it: 01/12/2022, 02/12/2022, 03/12/2022.

Image of the dataset:

My current thinking is to use PowerQuery but I'm stuck. Maybe there's a clever Excel function that can do what I need. Any suggestion/ help would be awesome. Many thanks!

Answer 1

The trick is to read the two dates as numbers.

1. Read the start and end dates as numbers Int64.Type
2. Make a new column and create a list/range with {[Date start]..[Date end]}
3. Expand the list to multiple rows with Table.ExpandListColumn
4. Switch back the three dates to date type

Try this :

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"A C Title", type text}, {"Date start", Int64.Type}, {"Date end", Int64.Type}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each try {[Date start]..[Date end]} otherwise null),
    #"Expanded Custom" = Table.ExpandListColumn(#"Added Custom", "Custom"),
    #"Changed Type1" = Table.TransformColumnTypes(#"Expanded Custom",{{"Custom", type date}, {"Date start", type date}, {"Date end", type date}})
in
    #"Changed Type1"

# Output :