Unpivot data in Power Query based on Date range

[MohsinBipu]

Dear Experts,

In my table I have StartDate and EndDate column. 

Based on this, Each row will be created for this date range. 

Please see the input and output table.

Can you please help?

[Omid_Motamedise]

Hi,

Try this formula

let
Source = Excel.CurrentWorkbook(){[Name="Table3"]}[Content],
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Start Date", type date}, {"End Date", type date}}),
#"Merged Columns" = Table.CombineColumns(#"Changed Type",{"Start Date", "End Date"},each (if _{1}=null then _{0} else List.Dates(_{0},Duration.Days(_{1}-_{0}),#duration(1, 0, 0, 0))),"Date"),
#"Expanded Date" = Table.ExpandListColumn(#"Merged Columns", "Date")
in
#"Expanded Date"

[ahadkarimi]

@MohsinBipu, add a Custom Column with this formula and let me know if there is any problem:

List.Dates([Start date], Duration.Days([End date] - [Start date]) + 1, #duration(1, 0, 0, 0))

Expand the new column to generate rows for each date.
Remove the original "Start date" and "End date" columns if not needed.

[dufoq3]

Hi, don't add +1 to Duration.Days --> based on example - he doesn't want to return dates till 14/08 but only to 13/08 for the first row.

[ahadkarimi · Accepted solution]

Hey @dufoq3, you’re right. The final version will be:

List.Dates([Start date], Duration.Days([End date] - [Start date]), #duration(1, 0, 0, 0))