Transfer [h]:mm:ss duration to PBI

[timazarj]

Hi,

Some of the columns in my excel source are in [h]:mm: ss format:

when I transferred to PBI, it appeared as :

 I already tried to transfer the column to Time only. But It changed all the hours from 1 to 24. example: 126:05:18 changed to "1900-01-05 6:05:18"

I also tried extracting the hours in excel using the HOUR function, but it only returns the hour between 1 and 24.

I would appreciate it if anybody could help me figure out how to keep the duration as it is. 

[v-xiaotang]

Hi @timazarj 

I just want to confirm if you resolved this issue? If yes, you can accept the answer helpful as the solution or share you method and accept it as solution, thanks for your contribution to improve Power BI.

If you need more help, please let me know.

Best Regards,

Community Support Team _Tang

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

[CNENFRNL]

The corresponding native data type in PQ is duration; but it doesn't display more than 24h,

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMja3MjawMjVVitWJVjI0MrMyMLUytFCKjQUA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Total Time" = _t]),
    Duration = Table.AddColumn(Source, "Dur", each let l=List.Transform(Text.Split([Total Time], ":"), Number.From) in #duration(0,l{0},l{1},l{2}), type duration)
in
    Duration

[rsbin]

@timazarj ,

Power BI obviously does not handle this time format well.

The way I handle this is to convert your Duration to Seconds in your source table.

In Power BI, you can then use a DAX formula to convert Seconds back into your hh:mm:ss format with DAX.

I don't have this at my fingertips at the moment, but can provide to you later today....if you are unable find something on this Board.

Hope this gets you pointed in the right direction.

Regards,