Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hi team,
I need to get the hours in decimals between two timestamps. What is the M code for this?
Example:
SHIFTSTARTDATETIME | SHIFTENDDATETIME | Hours |
9/11/2022 2:45:00 PM | 9/11/2022 11:15:00 PM | 8.5 |
1/12/2022 10:30:00 PM | 2/12/2022 7:00:00 AM | 8.5 |
Thanks
Carl
Solved! Go to Solution.
Solved this through a few steps:
#"Added Custom" = Table.AddColumn(#"Inserted Time1", "Custom", each [SHIFTENDDATETIME]-[SHIFTSTARTDATETIME]),
#"Inserted Hours" = Table.AddColumn(#"Added Custom", "Hours", each Duration.Hours([Custom]), Int64.Type),
#"Inserted Minutes" = Table.AddColumn(#"Inserted Hours", "Minutes", each Duration.Minutes([Custom]), Int64.Type),
#"Added Custom1" = Table.AddColumn(#"Inserted Minutes", "Hours (30 min lunch)", each ([Hours]+([Minutes]/60))-.5),
#"Removed Columns" = Table.RemoveColumns(#"Added Custom1",{"Custom", "Hours", "Minutes"})
Ah yes, correct! Then use the Duration.TotalSeconds: https://learn.microsoft.com/en-us/powerquery-m/duration-totalseconds
It will be easier to use I think.
Solved this through a few steps:
#"Added Custom" = Table.AddColumn(#"Inserted Time1", "Custom", each [SHIFTENDDATETIME]-[SHIFTSTARTDATETIME]),
#"Inserted Hours" = Table.AddColumn(#"Added Custom", "Hours", each Duration.Hours([Custom]), Int64.Type),
#"Inserted Minutes" = Table.AddColumn(#"Inserted Hours", "Minutes", each Duration.Minutes([Custom]), Int64.Type),
#"Added Custom1" = Table.AddColumn(#"Inserted Minutes", "Hours (30 min lunch)", each ([Hours]+([Minutes]/60))-.5),
#"Removed Columns" = Table.RemoveColumns(#"Added Custom1",{"Custom", "Hours", "Minutes"})
Hello @CarlBlunck , I think that what you need is one of the Duration functions. See documentation here: https://learn.microsoft.com/en-us/powerquery-m/duration-seconds
Your function could look like:
hours = Duration.Seconds(dateTime1-dateTime2) / 3600
Hope this helps.
Alexandre
Thanks @_AlexandreRM_ unfortuately all of the duration functions just extract the portion of the duration out. So duration.seconds will just give you the seconds of the calculated duration. Which in my case, returns 0.
Ah yes, correct! Then use the Duration.TotalSeconds: https://learn.microsoft.com/en-us/powerquery-m/duration-totalseconds
It will be easier to use I think.
Ha it pays to scroll down and read the rest of the available functions list. Thank mate, much cleaner. Went with the Duration.TotalHours function as still gives decimals.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.