Power BI - Using Power Query to calculate Duration

Colin Maitland, 03 February 2022

When configuring a Power BI data model, the addition of one or more new column to store a calculated duration between two Date/Time values in Days, Hours or Minutes, or perhaps just Total Hours, is sometimes required.

This can be done by adding Custom Columns using Power Query or Calculated Columns using DAX. The best practice approach is to move any such calculations as far back in the Data Extract, Transform and Load process as possible in the preferred order of a Data Source Column(s), Power Query Custom Column(s), and DAX Calculated Columns.

In this article I will demonstrate two method that might be used when using Power Query. The first example calculates and adds custom columns for the duration between a start and finish date/time in Days, Hours and Minutes, and the second calculates and adds a single custom column for the Total Hours.

Example 1: Elapsed Duration – Days, Hours, Minutes – Example

This example using the Duration.ToRecord() function to calculate the duration in Days, Hours and Minutes between the Started On and Finished On date/time values. This example results in the addition of three custom columns. One each for Days, Hours and Minutes.

This three-step example is based on these startedon and finishedon column values:

The first step calculates the Duration. This step results in the addition of a custom record type column named Elapsed Duration. The duration records contain attributes for Days, Hours, Minutes and Seconds that represent the duration between the startedon and finishedon values.

Step1 =

Table.AddColumn(PreviousStep, "Elapsed Duration", each

Duration.ToRecord([finishedon] - [started]))

The second step expands the Elapsed Duration record column into separate Elapsed Duration (Days), Elapsed Duration (Hours) and Elapsed Duration (Minutes) columns. It is optional, which columns from the Elapsed Duration you select to expand. In this example, I have chosen not to include Seconds.

Step2 =

Table.ExpandRecordColumn(Step1, "Elapsed Duration", {"Days", "Hours", "Minutes"}, {"Elapsed Duration (Days)", "Elapsed Duration (Hours)", "Elapsed Duration (Minutes)"})

The third step changes the data types for the Elapsed Duration (Days), Elapsed Duration (Hours) and Elapsed Duration (Minutes) columns from Any, i.e., ABC123, to Whole Number, i.e., 123.

Step3 =

Table.TransformColumnTypes(Step2, {{"Elapsed Duration (Days)", Int64.Type}, {"Elapsed Duration (Hours)", Int64.Type}, {"Elapsed Duration (Minutes)", Int64.Type}})

Example 2: Elapsed Duration – Days, Hours, Minutes – Example

This example using the Duration.TotalHours() function to calculate the total duration in Hours between the Started On and Finished On date/time values. This example results in the addition of one custom whole number column named Elapsed Duration (Total Hours).

This single-step example is based on these startedon and finishedon column values:

The step is as follows. You will notice that this step includes a clause to set the data type of the custom column to a Decimal Number.

Step =

Table.AddColumn(PreviousStep, "Elapsed Duration (Total Hours)", each Duration.TotalHours([finishedon] - [createdon]), type number)

Final Comments

In the preceding examples the date/time values were retrieved from Dynamics 365 and were Date/Time/Zone columns. These examples will work on Date/Time/Zone and Date/Time columns. It does not matter if you calculate the duration before or after converting the Date/Time/Zone columns to Date/Time columns. However, it is best practice when configuring a data model to only include Date columns in the data model. If you require the time portions as well, then they should be stored in a separate column. Therefore, after calculating the duration, you should then change the data type of the Date/Time/Zone or Date/Time columns to Date columns, after first taking care to convert the Date/Time/Zone columns to the correct Time Zone before then converting them to Date columns. You can refer to my Customer Power BI UTC to Local Date/Time with Daylight Saving Time Function article for additional insights on this point.