Power Query Duration format consistency

Question

i'm using Power Query in Excel to filter some .txt files, extract a time value from each and show the results. The problem is the original data which comes in 3 different formats:

3:42.807
0:01:48.19
3:46

how can i convert them in a consistent format (e.g. 3:42.80). I need to show minutes, seconds and eventually milliseconds.

I tried several different Duration.ToText or Duration.From but i had more problems. Also i'd like to avoid adding an extra column just to calculate the duration format. Is it possible to convert the original column only?

= Table.TransformColumnTypes(#"Reordered Columns",{{"Time", type duration}})

This is the formula from Power query i sued to convert to duration, but i don't like this format "d.hh.mm.ss" etc. it gives me errors. It would be perfect if it was a Duration type but only displaying minutes, seconds and milliseconds (3:41.23)

Any help please?

Alexis Olson Alexis Olson · Accepted Answer · 2020-09-03T17:59:59

I don't think you can change the displayed format for a given column type in the query editor. If you need a different format, then you'll have to convert it into a text column.

Here's how I tinkered with it:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMrYyMdKzMDBXitWJVjKwMjC0MrHQM7QEc4FyZkqxsQA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Time = _t]),
    StandardizeFormat = Table.TransformColumns(Source,{{"Time", each Duration.ToText(Duration.FromText(if List.Count(Text.Split(_, ":")) = 2 then "0:" & _ else _)), type text}}),
    TrimZeros = Table.TransformColumns(StandardizeFormat, {{"Time", each Text.TrimEnd(Text.AfterDelimiter(_, "00:0"),"0"), type text}})
in
    TrimZeros

The if List.Count(Text.Split(_, ":")) = 2 then "0:" & _ else _ part tells it to prepend 0: to the beginning if there aren't already two colons (so that the conversion do duration doesn't break).

Power Query Duration format consistency

1 Answers