[Power Query] How to Calculate Average Time and Add it to a Column | Supporting “mm:ss.0” Format

Introduction

I wanted to use Excel’s Power Query function to calculate the average of time data and add it as a column, so I learned and implemented the method. This technique is particularly effective when you want to process data in a user-defined format like “mm:ss.0”, which Excel doesn’t always recognize as standard time immediately.

Goal

• Time data is recorded in “mm:ss.0” format.
• I want to convert this data into seconds to calculate the average value.
• I want to add the average value as a new column to handle it within the query.

Defining a Function to Convert to Seconds

In Power Query (M Language), I first created a function to convert the time text string into a numerical value (in seconds).

Here is the code I used:

let
    // Definition of function to convert time data to seconds
    ConvertTimeToSeconds = (timeText as text) as number =>
        let
            TimeParts = Text.Split(timeText, ":"),
            Minutes = Number.From(Text.Start(TimeParts{0}, 2)),
            SecondsAndMilliseconds = Text.Split(TimeParts{1}, "."),
            Seconds = Number.From(SecondsAndMilliseconds{0}),
            Milliseconds = if List.Count(SecondsAndMilliseconds) = 2 then Number.From(SecondsAndMilliseconds{1}) / 1000 else 0,
            TotalSeconds = Minutes * 60 + Seconds + Milliseconds
        in
            TotalSeconds
in
    ConvertTimeToSeconds

Explanation of the Code

• Text.Split(timeText, ":"): Extracts the minutes.
• Text.Split(..., "."): Splits the seconds and milliseconds.
• Number.From(...): Converts the text string to a number.
• Calculation: Adds Minutes (x60), Seconds, and Milliseconds (divided by 1000) to convert everything into a single “Total Seconds” value.

Calculation of Average Value and Method of Adding to Column

After defining this function, I proceeded with the processing in the following steps:

1. Add a new column to the original table.
2. Use ConvertTimeToSeconds([TimeColumn]) to calculate the seconds for each row.
3. Calculate the Average of the added seconds column (using List.Average or Group By).
4. (Optional) Reformat the result back to “mm:ss.0” if necessary.

Summary

By using Power Query, it is possible to convert time data in “mm:ss.0” format into seconds on Excel and handle it as a numerical value. Once the function is defined, very flexible aggregation and averaging can be performed.

• Custom Function: Converts text time to seconds.
• Calculation: Average and Sum values can be easily calculated once converted.
• Accuracy: Handling data as numeric values improves the precision of statistical processing and filtering.

I hope this article serves as a reference for those tackling similar tasks.