Dynamics F/SCM Customer Experience (CRM) Dynamics GP Dynamics Business Central (NAV) Dynamics SL Power Platform Fabric Copilot

[Vishesh Jain]

Hello everyone,

Does anyone have any articles or videos or any other resources regarding the ‘Duration’ and ‘Time’ functions in M for Power Query (Query Editor).

Please share with the community if you have any.

Thank you,

——————————
Vishesh Jain
Owner
VR Construction
——————————

[Clayton Groom]

,

A good place to always start is the Power Query documentation:

?https://docs.microsoft.com/en-us/powerquery-m/duration-functions
https://docs.microsoft.com/en-us/powerquery-m/time-functions
or https://www.powerquery.io/duration

The input to the DURATION functions is a duration value, derived by subtracting one Date/Time value from another.
For an example, you could create a custom column in Power Query called DurationMinutes and set its calculation to:

=Duration.TotalMinutes([EndDate] - [BeginDate])?

A duration value is expressed in Days, Hours, Minutes, and Seconds, and can be represented in a query using #duration() to convert literal values to a duration:

Duration.Minutes(#duration(4, 13, 5, 23))

Hope that helps!

Clayton

——————————
Clayton Groom
President
SQL Monger
Smithton IL
——————————
——————————————-

[Vishesh Jain]

Hi ,

Thank you for responding.

If I already have my durations in the HH:MM format in my source file, how do I convert it to duration?

I tried using the data type ‘Time’ but if the duration goes over 24 hours, it gives me an error and I can’t perform any calculation on it.

Please let me know, if you have a solution or if there is any other source that I can refer to.

Thank you,

——————————
Vishesh Jain
Owner
VR Construction
——————————
——————————————-

[Clayton Groom]

,

If you can split that ?duration in your source file into Hours and Minutes, then you can use the #duration() function to convert it to a duration value.

#duration(days, hours, minutes, seconds))

?is the format. if you only have the hours and minutes, plug zeros into the other elements. Given columns of SourceHours and SourceMinutes, the code for a custom column to return duration in minutes would be:

Duration.Minutes(#duration(0, SourceHours, SourceSeconds, 0))?

Warm regards,

Clayton

——————————
Clayton Groom
@SQLMonger
——————————
——————————————-

[Simon Lamb]

Hi ,

By far the easiest way of calculating the duration in minutes is if you have the start and end DateTime fields:

#"Inserted Time Subtraction" = 
    Table.AddColumn(#"Changed Type", "Subtraction", each [DateTime2] - [DateTime1], type duration),
#"Inserted Total Minutes" = 
    Table.AddColumn(#"Inserted Time Subtraction", "TotalMins", each Duration.TotalMinutes([Subtraction]), Int64.Type),
?

?The Subtraction field in Power Query has the format d.hh:mm:ss but in the report you can’t display it in a table like that – in fact I can’t see much use for it at all for visualisation purposes.  The duration in minutes is much more useful and presumably what you want.

If you don’t have the start and end times then (assuming your Duration is a text field) you’ll need to split on delimiters then calculate minutes:

#"Duplicated Column" = 
    Table.DuplicateColumn(#"Inserted Total Minutes", "Duration", "Duration - Copy"),
#"Split Column by Delimiter" = 
    Table.SplitColumn(#"Duplicated Column", "Duration - Copy", Splitter.SplitTextByDelimiter(":", QuoteStyle.Csv), 
    {"Hours", "Minutes", "Seconds"}),
#"Changed Type1" = 
    Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Hours", Int64.Type}, {"Minutes", Int64.Type}, {"Seconds", Int64.Type}}),
#"Added Custom" = 
    Table.AddColumn(#"Changed Type1", "TotalMins", each [Hours3]*60 + [Minutes] + [Seconds]/60, type number)
?

I’ve duplicated the Duration column to keep the original but if it’s not any use to you in the final report then just split the original.  If you don’t have seconds then you can ignore the last element of the calculation.  Keep or delete the Hours, Minutes and Seconds columns as you see fit.

Hope that helps,

——————————
Simon Lamb
IT Consultant, Method Excel Ltd.
——————————
——————————————-

[Vishesh Jain]

Thank you so much and for your responses.

It helps a lot in understanding how duration works.

Thank you,??

——————————
Vishesh Jain
Owner
VR Construction
——————————
——————————————-

[Nathan Watkins]

You can change the time to decimal number type, and then change to duration type. Done!

——————————
Nathan Watkins
——————————
——————————————-