Getting all dates between 2 dates/times

[Anonymous]

I have two columns which start and end date/times. I am looking to calulates the dates that are inbetween these dates. I have achieved this with a custom column using the following:

= {Number.From([StartDate])..Number.From([EndDate])}

However, this doesnt work if the day includes a half day. Is there a way to put an expection on this? as it gives me and error for converting the int as its a half value. 

[v-yanjiang-msft · Accepted solution]

Hi @Anonymous ，

According to your description, here’s my solution.

1.Do the following steps in PowerQuery.

let

    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjIwMtI3MNQ3NFAwMLACIyUdhKgZQjRWB6waKAhEFgqGRlbGCNVwUXOY6lgA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [StartDate = _t, EndDate = _t]),

    #"Changed Type" = Table.TransformColumnTypes(Source,{{"StartDate", type datetime}, {"EndDate", type datetime}}),

    #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each List.Dates(DateTime.Date([StartDate]),Duration.Days(DateTime.Date([EndDate]) -DateTime.Date([StartDate])), #duration(1,0,0,0))),

    #"Expanded Custom" = Table.ExpandListColumn(#"Added Custom", "Custom")

in

#"Expanded Custom"

2.Create a column for the result.

Best Regards,
Community Support Team _ kalyj

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[amitchandak]

@Anonymous , Try in power query like

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

[Anonymous]

Hi @amitchandak would it be possible to add the date in the value if its a half day for example so make the output for the half day be 18/11/2021 ??