Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
2 years ago
Solved

Unpivot data in Power Query based on Date range

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?   ...
Need Help
Tips and Tricks
  • ahadkarimi's avatar
    ahadkarimi
    2 years ago

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

     

Omid_Motamedise's avatar
Omid_Motamedise
Super User
1 year ago

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"