Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

Getting all dates between 2 dates/times

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. 

 

 

Need Help
  • v-yanjiang-msft's avatar
    v-yanjiang-msft
    4 years ago

    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.

3 Replies

  • amitchandak's avatar
    amitchandak
    Super User
    4 years ago

    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's avatar
      Anonymous
      Not applicable
      4 years ago

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

  • v-yanjiang-msft's avatar
    v-yanjiang-msft
    Community Support
    4 years ago

    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.