Forum Discussion
Extract Hours
Hi, I am getting time deviation column from data source of type text which has values like: 0 days 01:10:00 0 days 00:20:30 1 days 02:10:00 I need to calculate hours from this columns,...
jitpbi
I think you need to calculate the total hours of each line.
Paste below code in a blank query in the advance editor and follow the steps.
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WUorViVYyUEhJrCxWMDCwMjKwMjYAi4EJQ6iEkZWhgZUBUCIWAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"0 days 01:10:00" = _t]),
#"Demoted Headers" = Table.DemoteHeaders(Source),
#"Filtered Rows" = Table.SelectRows(#"Demoted Headers", each ([Column1] <> "")),
#"Trimmed Text" = Table.TransformColumns(#"Filtered Rows",{{"Column1", Text.Trim, type text}}),
#"Inserted Text Before Delimiter" = Table.AddColumn(#"Trimmed Text", "Days", each Text.BeforeDelimiter([Column1], " "), type text),
#"Inserted Text Between Delimiters" = Table.AddColumn(#"Inserted Text Before Delimiter", "Text Between Delimiters", each Text.BetweenDelimiters([Column1], "days", ":"), type text),
#"Changed Type" = Table.TransformColumnTypes(#"Inserted Text Between Delimiters",{{"Days", Int64.Type}, {"Text Between Delimiters", Int64.Type}}),
#"Renamed Columns" = Table.RenameColumns(#"Changed Type",{{"Text Between Delimiters", "Hours"}}),
#"Added Custom" = Table.AddColumn(#"Renamed Columns", "Total Hours", each [Days] * 24 + [Hours])
in
#"Added Custom"
________________________
Did I answer your question? Mark this post as a solution, this will help others!.
Click on the Thumbs-Up icon on the right if you like this reply 🙂