Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

Reply
Baddeck
Frequent Visitor

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

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.

1 ACCEPTED SOLUTION
v-rzhou-msft
Community Support
Community Support

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.

vrzhoumsft_0-1690766776624.png

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.

vrzhoumsft_1-1690766822046.png

 

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.

View solution in original post

2 REPLIES 2
v-rzhou-msft
Community Support
Community Support

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.

vrzhoumsft_0-1690766776624.png

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.

vrzhoumsft_1-1690766822046.png

 

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
Resolver III
Resolver III

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! 

AbhinavJoshi_0-1690392260580.png

 

Helpful resources

Announcements
Microsoft Fabric Learn Together

Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

April Fabric Community Update

Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.