How to Format Time Duration Values in Power BI Reports?

by

“How can I convert time duration values from decimal hours into hours and minutes format in Power BI? Our project tracking data currently shows durations as plain decimal numbers, such as 1.5 or 2.25, but we need to display them in a format that is easier for stakeholders to understand and interpret quickly.”

Understanding Time Duration Formatting Challenges

When time is expressed in raw decimal values like 1.5 or 2.25 hours, it can be difficult for non-technical stakeholders to intuitively grasp the duration represented. These values require mental conversion, which can slow down decision-making and reduce clarity. Power BI provides several effective techniques to transform these decimal hour values into more human-readable formats, such as “1:30” for one and a half hours or “2:15” for two hours and fifteen minutes. In this tutorial, we’ll demonstrate how to perform these conversions using both Power Query (M language) and DAX (Data Analysis Expressions) in Power BI Desktop 2023, ensuring flexibility depending on where in the data pipeline you prefer to handle formatting.

Project Time Tracking Data Model

For the purpose of this tutorial, we’ll be working with a sample table named "ProjectTasks" that contains the following columns relevant for tracking time:

  • TaskID (Text) – A unique identifier for each task
  • TaskName (Text) – The name or title of the task
  • PlannedDuration (Decimal) – The expected duration of the task in decimal hours
  • ActualDuration (Decimal) – The real, logged duration of the task in decimal hours
  • StartDate (DateTime) – The timestamp when the task began
  • CompletionDate (DateTime) – The timestamp when the task was completed

This structure provides the foundation for measuring planned vs. actual effort and applying formatting logic accordingly.

Converting Duration Values Step-by-Step

1. Open Power Query Editor and Add a Formatted Duration Column

let
    Source = ProjectTasks,
    ConvertedDuration = Table.AddColumn(
        Source, 
        "FormattedDuration",
        each Time.FromText(
            Text.From(Int([ActualDuration])) & ":" & 
            Text.From(Number.Round(([ActualDuration] - Int([ActualDuration])) * 60))
        )
    )
in
    ConvertedDuration

2. Create a DAX Measure for Dynamic Duration Formatting

Formatted Duration = 
VAR Hours = INT(ProjectTasks[ActualDuration])
VAR Minutes = INT((ProjectTasks[ActualDuration] - Hours) * 60)
RETURN Hours & ":" & FORMAT(Minutes, "00")

3. Add a Calculated Column for Static Display

Duration Display = 
FORMAT(INT([ActualDuration]), "00") & ":" & 
FORMAT(INT(([ActualDuration] - INT([ActualDuration])) * 60), "00")

4. For Advanced Logic, Create a Duration Format Function in Power Query

(DurationValue as number) as text =>
let
    Hours = Number.IntegerDivide(DurationValue, 1),
    Minutes = Number.Round((DurationValue - Hours) * 60),
    FormattedTime = Text.From(Hours) & ":" & 
                   Text.PadStart(Text.From(Minutes), 2, "0")
in
    FormattedTime

5. Apply Conditional Formatting to Visualize Duration Insights

  • Select your duration column or DAX measure in a visual (e.g., table or matrix)
  • Navigate to Column Tools > Conditional Formatting
  • Define custom formatting rules, for instance, coloring tasks exceeding 3 hours in red

Pro Tip: If your reports are being consumed internationally, use Power BI’s locale settings to align date and time display formats with regional preferences, avoiding confusion in time interpretation.

Common Time Duration Formatting Issues

Missing or Invalid Duration Values

  • Error: "Expression.Error: Invalid duration value"
  • Solution: Add error handling in Power Query to replace null or malformed values with a default of 0 using try...otherwise
  • In DAX, use IFERROR() or ISBLANK() checks to gracefully manage edge cases

Decimal Precision Problems

  • Issue: Minor rounding discrepancies in minute conversion (e.g., showing “1:59” instead of “2:00”)
  • Fix: Use ROUNDUP() or ROUNDDOWN() depending on your desired accuracy, and consider formatting values consistently to one or two decimal places before transformation

Format Consistency

  • Challenge: Source data may contain a mix of integer, decimal, or even string values
  • Resolution: Normalize all duration values to decimal hour format first before applying formatting
  • Introduce a validation or cleaning step in Power Query to enforce data type consistency across records