Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

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. 

 

dandan123456_0-1636649207831.png

 

1 ACCEPTED SOLUTION
v-yanjiang-msft
Community Support
Community Support

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"

vkalyjmsft_0-1636968861914.png

 

2.Create a column for the result.

vkalyjmsft_1-1636968861918.png

 

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.

View solution in original post

3 REPLIES 3
v-yanjiang-msft
Community Support
Community Support

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"

vkalyjmsft_0-1636968861914.png

 

2.Create a column for the result.

vkalyjmsft_1-1636968861918.png

 

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
Super User
Super User

@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
Not applicable

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

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.