Forum Discussion
Helper Iduration above 24h into decimal
Dear Group, I am new on Power BI. I would like to transform columns Durata and Tempo in decimal or duration in Power query editor. I saw different solutions, but I was not able to do it. Can anyone help?
Time is expressed in hours:minutes
Appreciated and thankful.
Marabeda
Replace entire yellow line with this (The formula which had given was to be inserted in a custom column which will generate below code)
#"Personale" = Table.AddColumn(#"Modificato tipo", "Custom", each [n=Text.Split([Durata],":"), d=#duration(Number.IntegerDivide(Number.From(n{0}),24),Number.Mod(Number.From(n{0}),24),Number.From(n{1}),0)][d])Thank you very much. It works!!😁
11 Replies
marabedaThank you for your suggestion but if I use duration I have an Error because 55 hours are above 24h.
At the end, I need to sum up for each employees the hours and minutes worked.
- tackytechtom
Most Valuable Professional
Hi marabeda ,
I interpret that you'd like to convert the time into decimals as duration:
Here the code in Power Query M that you can paste into the advanced editor (if you do not know, how to exactly do this, please check out this quick walkthrough)
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjC2MjVUitUBsoysjA0gLEMrQyMwy9DEytAUwrK0MjJWio0FAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Time = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"Time", type time}}), #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each Time.Hour([Time]) + Time.Minute([Time]) / 60) in #"Added Custom"Hope this helps 🙂
/Tom
https://www.tackytech.blog/
https://www.instagram.com/tackytechtom/- marabeda
Thank you Tomfox, but because I am not an expert I copied and pasted your code in advance editor. The result is an error:
I do not have the knwoledge to understand the first line.
Can you explain me?
- mahoneypat
Microsoft Employee
- Vijay_A_Verma
Use below formula to convert to duration for Durata. For Tempo, replace Durata with Tempo.
= [n=Text.Split([Durata],":"), d=#duration(Number.IntegerDivide(Number.From(n{0}),24),Number.Mod(Number.From(n{0}),24),Number.From(n{1}),0)][d]- marabeda
Thank you, Vijay, how can I insert your code after this code?
I do not think is correct because below I have an error.
Can you explain to me? Thank you.
- Vijay_A_Verma
You will need to put a comma at the end of line before yellow highlighted line.
In PQ, comma has to be after all lines except the line after and before in. Hence, Yellow highlighted line will not have comma but line preceding it will have.
