Extract Hours

[jitpbi]

 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, please help to achieve this.

Note: I can't convert this text column into date/time or duration.

Thanks

[FrankAT]

Hi @jitpbi ,

create a new column (see figure [1]).

Time = RIGHT('Table'[Deviation],8)

 Use the following measure and select the data type (see figure [2]).

Sum of Deviation = SUM('Table'[Time])

 Regards FrankAT

[mahoneypat]

Please add a custom column in the query editor with this formula.  It will give you the total hours, including partial/decimal hours.

= Number.From(Text.BeforeDelimiter([Column1], " "))*24 + Duration.TotalHours(Duration.FromText(Text.AfterDelimiter([Column1], " ", 1)))

If this works for you, please mark it as the solution.  Kudos are appreciated too.  Please let me know if not.

Regards,

Pat

[Greg_Deckler]

@jitpbi - You could create a new column that took the value of your old column and then just replace " days " with ":" and then you might be able to convert that to duration.

[Fowmy]

@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 🙂

YouTube, LinkedIn