Expand Dates to complete ranges

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!