Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Hi there
I have two datetime columns:
Power Query recognizes the column data types as datetimes. However, I am not sure if the formatting (probably regional) causes problems. When I add this custom column in Powre Query I get numbers out that looks about right although they are in a horrible format:
Result:
But when I try to convert the column type to datetime to hopefully get a format that's easier on the eye, I get the following error:
Changing the column type to duration makes no difference to the values, and the type time returns the same error as above.
I also tried to solve the problem in DAX by concatenating 4 types of DATEDIFF (days, hours, minutes and seconds - why the heck don't they have an easier DAX formula for that instead of having to use 4 DATEDIFF expressions?!). This creates other issues, such as returning the value 1 day if the datetime value shifts from just before midnight to after midnight (instead of returning the value 1 only after 24 hours of duration).
I want the following result:
Time difference bwteen lastRefresh_startTime 15-02-2023 23:09:43 and lastRefresh_endTime 16-02-2023 02:16:24 =
0 days, 3 hours, 6 minutes, 41 seconds
or even better:
00:03:06:41
Thanks in advance
Solved! Go to Solution.
Hi,
i think your problem is Milliseconds which are in your datetime.
You have to extract Text before delimiter in each column using . as delimiter
(and if you prefer in the same step change type text to type datetime)
= Table.TransformColumns(#"Changed Type", {{"Column1", each Text.BeforeDelimiter(Text.From(_, "it-IT"), "."), type datetime}})
then you use your formula
and it will be all right
When you change finally the type you need to use Duration and not Datetime.
If this post is useful to help you to solve your issue, consider giving the post a thumbs up and accepting it as a solution!
p.s.: pay attention to the fact that you will not be able to use duration in your report (when you load your duration is converted to decimal). Refer to this in order to handle this problem
https://community.powerbi.com/t5/Desktop/Duration-format/m-p/447543
Hi,
i think your problem is Milliseconds which are in your datetime.
You have to extract Text before delimiter in each column using . as delimiter
(and if you prefer in the same step change type text to type datetime)
= Table.TransformColumns(#"Changed Type", {{"Column1", each Text.BeforeDelimiter(Text.From(_, "it-IT"), "."), type datetime}})
then you use your formula
and it will be all right
When you change finally the type you need to use Duration and not Datetime.
If this post is useful to help you to solve your issue, consider giving the post a thumbs up and accepting it as a solution!
p.s.: pay attention to the fact that you will not be able to use duration in your report (when you load your duration is converted to decimal). Refer to this in order to handle this problem
https://community.powerbi.com/t5/Desktop/Duration-format/m-p/447543