Need expression explained please

[Jtischler]

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

[KNP · Accepted solution]

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
    Invoked

What 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 · Accepted solution]

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. 

[KNP · Accepted solution]

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
    Invoked

What 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) 

[Jtischler]

@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!

[KNP]

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.

[Jtischler]

@KNP   Ahh ok, that makes sense on the zero based index. Thank you.

I thought DAX was the language used in PowerQuery expressions?

Your list visual helped as well to understand how the magic was happening behind the scenes as well 🙂

Here is the sample data that was causing the issues that the expression solved for 

Duration (HH:MM) for Reporting

161:48:00

161:48:00

161:48:00

161:48:00

161:48:00

161:48:00

161:48:00