Power Query: Get all dates between the Start and End date

Problem Statement: Data has been provided with the date range in each row. Data need to be distributed or expanded for all the dates in the range.

The old DAX solution you can find here.

The new solution is needed using the Power Query.

In Power Query, we will use List.Dates to generate the dates between two dates. In the same way, we generate a calendar. But for each row and the expand the list into rows.

Added custom column

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

Code

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WclTSUTLUN9Q3MjAygDANIOxYnWglJ6CIEUISyDRESDqDRUyxS7oARYzhZoHZUJWxsQA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Emp = _t, #"Start Date" = _t, #"End Date" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Emp", type text}, {"Start Date", type date}, {"End Date", type date}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Dates", each List.Dates([Start Date], Duration.Days([End Date] -[Start Date]) +1 ,#duration(1,0,0,0) )),
    #"Expanded Dates" = Table.ExpandListColumn(#"Added Custom", "Dates"),
    #"Changed Type1" = Table.TransformColumnTypes(#"Expanded Dates",{{"Dates", type date}})
in
    #"Changed Type1"

The final outcome

You can watch the video here.

Click Here to access all my blogs and videos in a jiffy via an exclusive visual glossary using Power BI.
Please like, share, and comment on these. Your suggestions on improvement, challenges, and new topics will help me explore more.

You Can watch my Power BI Tutorial Series on My Channel, Subscribe, Like, and share