Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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.
Solved! Go to 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.
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.
@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))
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 ??
User | Count |
---|---|
126 | |
108 | |
99 | |
65 | |
62 |
User | Count |
---|---|
136 | |
113 | |
102 | |
71 | |
60 |