Problem with Time format

[Anonymous]

Hello everybody,

I am having real problems while managing hours. I have an Excel file with some columns with houer type. Whenever it goes over 24 hours, Power Query transforms it to a date / hours, but in the desktop I only got the rest of hours within a day. Power BI truncates the rest of days (whenever there are...). 

Anyway to solve this? I am getting really crazy with this. I really need a solution, this is for my daily work. Thanks a lot!!!

[ppm1]

Please see this article for a good/flexible way to handle this.

Calculate and Format Durations in DAX – Hoosier BI

Pat

[lbendlin]

Change the column type to numerical.  Multiply by 24 if you need hours and hour fractions insteadof the default day fractions.

[Anonymous]

Hi @lbendlin ,

Thanks a lot. I've tried it, but it does not work. Power BI does not make the conversion correctly. For example, I have 75:31:00 hours in Excel. When I get it in Power Query, I get 03/01/1900 3:31:00. Then I convert it to decimal number, and it shows "4.00" (days), when i should get "3.15" (APROX)...

[lbendlin]

seems to work for me.

let
    Source = #table({"Column1"},{{#duration(0,75,31,0)}}),
    #"Changed Type1" = Table.TransformColumnTypes(Source,{{"Column1", type number}})
in
    #"Changed Type1"

Can you post a sample Excel file?