Forum Discussion
Helper IFormat Duration int to Datetime D HH:mm:SS
Hi, i am strungligh to get this formula, since i converted my datetime to duration and now i would like to Sum the duration and the outputformat would be D HH:mm: SS (Days Hours Minutes Seconds)
Format of DateTime Column = DD/MM/YYYY hh:mm: SS
(example 02/02/2002 20:30:00)
(example 02/02/2002 20:50:00)
...
Formula to get Duration From my DateTime Column:
My duration column got values like this
10
20
10
10
10
10
10
10
10
10
...
Now i can SumDuration and Set format like this (DD HH:mm: SS) is there a easy to do this ?
Just to make sure everything is in order. Your original field, which expressed the data in Minutes as a whole number, will be of data type 'Whole Number', but all subsequent fields should be either a decimal number type, or a date/time style number type.
Duration is only a datatype within Edit Queries (Power Query Language, known as M), but is not a data type you can select in the Power BI Data Model itself. Duration is best kept as a decimal number, but you can format it yourself when trying to display on a report. This could be done using another measure and the FORMAT statement, or by using some math tricky to build out a TEXT value to display how you wish.For example:
Display Duration = VAR hours = FLOOR([SumNewDurationINConditoon] * 24, 1) var minutes = FLOOR(([SumNewDurationINConditoon] * 24 * 60) - (hours * 60), 1) RETURN hours & ":" & minutes
13 Replies
Create a new blank query and pop this code in (The source line is just an Enter Data). This should give you an idea how to convert Minutes into Duration
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjJQitWJVjKGUKZAKhYA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Time = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"Time", Int64.Type}}), #"Added Custom" = Table.AddColumn(#"Changed Type", "AsTime", each [Time] / 24 / 60), #"Changed Type1" = Table.TransformColumnTypes(#"Added Custom",{{"AsTime", type duration}}) in #"Changed Type1"You could select "DateTime" instead of duration if you wished.
BlastSHi Anonymous
I am not truly understand how can i do this with the code you provide, on source speacialy.
I need to create new "SubTable" on my powerquery ? and somehow apply those steps?
If possible could you provide a example in a pbix file ?
Best regards,
Blasts
The source line just sets up the sample data. If you click through the applied steps, you'll get an idea of how i'm transforming the data into the new column. The idea is that you can take the method and apply it to your existing tables.
Really all i'm doing is taking your minutes as a whole number and dividing them by 60 and by 24. This gets you a number in the format that is expected by Duration. Duration is in a format where a whole day is 1. Therefore half a day (12 hours) is 0.5. To convert minutes as a whole number, you divide by 60 and divide again by 24.
I needed the H:mm datatype to represent a duration derived from subtracting Date/time types, but when I tried to use that datatype to create a calculated column-eekkk yikes, Power BI gave me the wrong data. Segments of the 24th hr or something, Work. So I went back to my excel file, made the calculated column from there, re-uploaded and like magic, no datatype conversion issues.