Expand Dates to complete ranges

[joshua1990]

Hi all!

I have a transactional table that is displays when a case was opened and when it was closed.

Case	Date Start	Date Closed
AAA1	01.01.2022	01.01.2023
AA2	05.02.2022	12.03.2022
AA21	06.07.2022

Now I would like to expand this table in a way that I get a row for each date betweend Date Start and Date closed.

If there is no Date Closed defined, then it should be expanded to today.

Example

Case	Date
AA2	05.02.2022
AA2	06.02.2022
AA2	07.02.2022
AA2	...
AA2	12.03.2022

How would yo do that in Power Query?

[wdx223_Daniel]

=#table({"Case","Date"},List.TransformMany(Table.ToRows(PreviousStepName),each List.Dates(_{1},Duration.Days(_{2}-_{1}),Duration.From(1)),(x,y)=>{x{0},y}))

[v-jingzhang · Accepted solution]

Hi @joshua1990 

You can add a custom column with below code, then expand the list column to new rows. 

let
    _startDate = [Date Start], 
    _endDate = if [Date Closed] = null then Date.From(DateTime.LocalNow()) else [Date Closed] 
in
    List.Generate(()=>[x=_startDate], each [x]<=_endDate, each [x=Date.AddDays([x],1)], each [x])

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WcnR0NFTSUTIyMDLSM9SDM42BzFgdsLQRXNpIz8gMyFGKjQUA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Case = _t, #"Date Start" = _t, #"Date Closed" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Case", type text}, {"Date Start", type date}, {"Date Closed", type date}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each let
    _startDate = [Date Start], 
    _endDate = if [Date Closed] = null then Date.From(DateTime.LocalNow()) else [Date Closed] 
in
    List.Generate(()=>[x=_startDate], each [x]<=_endDate, each [x=Date.AddDays([x],1)], each [x])),
    #"Expanded Custom" = Table.ExpandListColumn(#"Added Custom", "Custom")
in
    #"Expanded Custom"

You can also add a custom column with below code then expand it to new rows. 

List.Dates([Date Start], Duration.TotalDays((if [Date Closed] = null then Date.From(DateTime.LocalNow()) else [Date Closed]) - [Date Start]) + 1, #duration(1,0,0,0))

Best Regards,
Community Support Team _ Jing
If this post helps, please Accept it as Solution to help other members find it. Highly appreciate your Kudos!

[ImkeF]

Hi @joshua1990 ,
this function does the job: Date.DatesBetween to retrieve dates between 2 dates in Power BI and Power Query – The BIccountant