How to handle time columns? (ie "transaction time" & "wait time")

[Baddeck]

I have "transaction time" & "wait time" type columns in Excel with "mm:ss" & "hh:mm:ss" formats. Powerbi does not recognize the data as times.

is there a standard approach to handling time data in PBI, displaying in visuals, and calculating averages & sums?

My thought is to convert to decimals for calculations then display results in a time format. I'm unclear on the last step.

[Anonymous · Accepted solution]

Hi @Baddeck ,

Please translate your table as below. Add two second columns in your table in Power Query Editor.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WclTSUTI0sDIyANIGpkDaytBMKVYnWskJJGFmZWgBkjC3MjC2MjQASzgDBUzMrYxBOoxMrQyNwBKxAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Category = _t, #"transaction time (mm:ss)" = _t, #"wait time (hh:mm:ss)" = _t]),
    #"Duplicated Column" = Table.DuplicateColumn(Source, "wait time (hh:mm:ss)", "wait time (hh:mm:ss) - Copy"),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Duplicated Column", "wait time (hh:mm:ss) - Copy", Splitter.SplitTextByDelimiter(":", QuoteStyle.Csv), {"wait time (hh:mm:ss) - Copy.1", "wait time (hh:mm:ss) - Copy.2", "wait time (hh:mm:ss) - Copy.3"}),
    #"Changed Type" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Category", type text}, {"transaction time (mm:ss)", type text}, {"wait time (hh:mm:ss)", type text}, {"wait time (hh:mm:ss) - Copy.1", Int64.Type}, {"wait time (hh:mm:ss) - Copy.2", Int64.Type}, {"wait time (hh:mm:ss) - Copy.3", Int64.Type}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "wait time seconds", each [#"wait time (hh:mm:ss) - Copy.1"] * 3600 + [#"wait time (hh:mm:ss) - Copy.2"]* 60 + [#"wait time (hh:mm:ss) - Copy.3"]),
    #"Duplicated Column1" = Table.DuplicateColumn(#"Added Custom", "transaction time (mm:ss)", "transaction time (mm:ss) - Copy"),
    #"Split Column by Delimiter1" = Table.SplitColumn(#"Duplicated Column1", "transaction time (mm:ss) - Copy", Splitter.SplitTextByDelimiter(":", QuoteStyle.Csv), {"transaction time (mm:ss) - Copy.1", "transaction time (mm:ss) - Copy.2"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter1",{{"transaction time (mm:ss) - Copy.1", Int64.Type}, {"transaction time (mm:ss) - Copy.2", Int64.Type}}),
    #"Added Custom1" = Table.AddColumn(#"Changed Type1", "transaction time seconds", each [#"transaction time (mm:ss) - Copy.1"]*60 +[#"transaction time (mm:ss) - Copy.2"]),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom1",{"wait time (hh:mm:ss) - Copy.1", "wait time (hh:mm:ss) - Copy.2", "wait time (hh:mm:ss) - Copy.3", "transaction time (mm:ss) - Copy.1", "transaction time (mm:ss) - Copy.2"})
in
    #"Removed Columns"

Result is as below.

Measure:

Sum of wait time (hh:mm:ss) = 
VAR _totalhh = FORMAT(QUOTIENT(SUM('Table'[wait time seconds]),3600),"00")
VAR _totalmm = FORMAT(QUOTIENT(SUM('Table'[wait time seconds]) - _totalhh*3600,60),"00")
VAR _totalss = FORMAT(SUM('Table'[wait time seconds]) - _totalhh*3600 -_totalmm*60,"00")
RETURN
COMBINEVALUES(":",_totalhh,_totalmm,_totalss)

 Result is as below.

Best Regards,
Rico Zhou

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[AbhinavJoshi]

Hello @Baddeck. You can conver the data to date/time/timezone in Power BI. Navigate to Power Query Editor -> Right click on the column that has date values and click on change type. Let me know if it works for you!