Forum Discussion
Helper IFormat Duration int to Datetime D HH:mm:SS
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
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.
Helper IHi 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.
- BlastS
Anonymous
I did a few trys on source and didnt work out, i tryed for example put the source = to TableA.DaTeTime and others trys and no info show. (My duration column was created outside powerQuery and for that so i will only have DateTime column to work with)
I clearly understand the nexts steps that i need to do after the source, seems logical and easy but this one i am stuck, and for what i understand the source is like my input still probably i am writing it wrong dont know.
Sorry bother you again but if you can help to define the source i would apreciate it very much.
Power Query has to happen first, so if your duration column was being created afterward, in DAX, then you'll need to work out a different method of getting that data inside Power Query.
Alternatively, if thats not possible, you could create this column in DAX instead using a similar idea. Try adding a new column and going:
YourNewDurationColumn = DIVIDE([YourOldColumn], 1440)
Note: Dividing by 1440 is the same as dividing by 60 then by 24