Forum Discussion
Need expression explained please
I found this formula in another post that takes text duration and converts it and it works perfect to give total hours sum, but I don't understand what its doing and I would like to gain that understanding. May someone please explain each line?
let
DurationFromYourText = (text as text) as duration => let
Split = Text.Split(text, ":"),
AsNumbers = List.Transform(Split, Number.FromText),
Duration = #duration(0, AsNumbers{0}, AsNumbers{1}, AsNumbers{2})
in
Duration,
Invoked = DurationFromYourText([UpTime])
in
Invoked
And what this does differently for the duration line?
Please remove the AsNumbers{2} by setting to 0. or change to the following
AsNumbers{0}, AsNumbers{1}, try AsNumbers{2} otherwise 0
Hi Jtischler,
let DurationFromYourText = (text as text) as duration => let Split = Text.Split(text, ":"), //splits the text duration field into a list for every ':' it finds AsNumbers = List.Transform(Split, Number.FromText), //sets the type for each item in the list to a number Duration = #duration(0, AsNumbers{0}, AsNumbers{1}, AsNumbers{2}) //creates an actual duration field from the items in the list. Assuming the field is hours, minutes, seconds. Power Query uses zero based index, so 0 is the first item, 1 the second etc. in Duration, Invoked = DurationFromYourText([UpTime]) //uses a column called 'UpTime' in the function to convert in InvokedWhat format is your text in for duration, not hours:minutes:seconds ?
Paste some example data.
You can just do this if you want 0.
Duration = #duration(0, AsNumbers{0}, AsNumbers{1}, 0)Glad I could help.
M is the language used in Power Query, DAX is everything to do with creating measures, calculated columns, tables.
If this is solved please accept solution on one of my answers above.
5 Replies
Hi Jtischler,
let DurationFromYourText = (text as text) as duration => let Split = Text.Split(text, ":"), //splits the text duration field into a list for every ':' it finds AsNumbers = List.Transform(Split, Number.FromText), //sets the type for each item in the list to a number Duration = #duration(0, AsNumbers{0}, AsNumbers{1}, AsNumbers{2}) //creates an actual duration field from the items in the list. Assuming the field is hours, minutes, seconds. Power Query uses zero based index, so 0 is the first item, 1 the second etc. in Duration, Invoked = DurationFromYourText([UpTime]) //uses a column called 'UpTime' in the function to convert in InvokedWhat format is your text in for duration, not hours:minutes:seconds ?
Paste some example data.
You can just do this if you want 0.
Duration = #duration(0, AsNumbers{0}, AsNumbers{1}, 0)KNP Thanks so much! May you explain a little more on the zero based index and why zero is listed twice here, once as a number?
#duration(0, AsNumbers{0}, AsNumbers{1}, AsNumbers{2})My text duration is in HH:MM, but many were over the 24 hour limit and were not summing up correctly. This expression worked perfect to convert the HHH:MM to a duration field where I could extract total hours to a decimal column and sum in a visual. I could follow some of the expression, but DAX is new to me so I didn't understand all of it. Thanks for the help!
Firstly, just for clarification, this is Power Query not DAX.
The #duration function has #duration(days, hours, minutes, seconds).
https://learn.microsoft.com/en-us/powerquery-m/sharpduration
The split function creates a list of numbers using ':' as a separator.
So, 12:25:33 becomes...
12 25 33 So, the code is saying always a hard coded 0 days, then the first item in the list for hours (AsNumbers{0}), the second for minutes (AsNumbers{1}) and the third for seconds (AsNumbers{2}).
Paste some examples of your text column and I'll provide some working code.
Glad I could help.
M is the language used in Power Query, DAX is everything to do with creating measures, calculated columns, tables.
If this is solved please accept solution on one of my answers above.
