Converting 1h 30m etc into time in PowerQuery

[Anonymous]

Hellooo!

The dataset I'm currently using exports time such as 10m or 1h 30m or 2d 5h 45m etc... 

I'm trying to convert this to a time type in PowerQuery but keep getting errors. Does anyone know how I can get Power BI to read this as 00:00:10 or 00:01:30 or 02:05:45 etc?

[Daviejoe]

This is an interesting one @Anonymous 

Can you display a small table to data please so we can see more examples of the data you are trying to convert?

[Anonymous]

@Daviejoe 

Time Spent

1h 30m

10m

10m

5h 45m

15m

30m

[Daviejoe · Accepted solution]

I think I got this working

let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMsxQMDbIVYrVATLRaNMMBRNTqBCUBiuNBQA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Time Spent" = _t]),
#"Lowercased Text" = Table.TransformColumns(Source,{{"Time Spent", Text.Lower, type text}}),
#"Inserted Text Before Delimiter" = Table.AddColumn(#"Lowercased Text", "Text Before Delimiter", each Text.BeforeDelimiter([Time Spent], "h ", {0, RelativePosition.FromEnd}), type text),
#"Inserted Last Characters" = Table.AddColumn(#"Inserted Text Before Delimiter", "Last Characters", each Text.End([Time Spent], 3), type text),
#"Replaced Value" = Table.ReplaceValue(#"Inserted Last Characters","m","",Replacer.ReplaceText,{"Last Characters"}),
#"Replaced Value1" = Table.ReplaceValue(#"Replaced Value","","0",Replacer.ReplaceValue,{"Text Before Delimiter"}),
#"Replaced Value2" = Table.ReplaceValue(#"Replaced Value1","","0",Replacer.ReplaceValue,{"Last Characters"}),
#"Inserted Merged Column" = Table.AddColumn(#"Replaced Value2", "Duration", each Text.Combine({[Text Before Delimiter], [Last Characters]}, ":"), type text),
#"Changed Type" = Table.TransformColumnTypes(#"Inserted Merged Column",{{"Duration", type duration}})
in
#"Changed Type"

[Daviejoe]

This was also a useful article I found

https://www.excelcampus.com/powerquery/convert-text-to-time-values-power-query/

[Anonymous]

Thank you!!

[Daviejoe]

No worries, I do love Power Query.  I still can't believe I was using Excel for years without knowing about it haha!

Have a great day @Anonymous 

[HotChilli]

Do you really want time type or do you want duration?

[Anonymous]

@HotChilli  Oh yeah duration, so I can total up the total time spent