Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
Jtischler
Helper I
Helper I

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

 

2 ACCEPTED SOLUTIONS
KNP
Super User
Super User

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) 

 

 

Have I solved your problem?
Please click Accept as Solution so I don't keep coming back to this post, oh yeah, others may find it useful also ;).
chrome-9xf-Zagzel-B

If you found this post helpful, please give Kudos.
It gives me a sense of instant gratification and, if you give me Kudos enough times, magical unicorns will appear on your screen.
If you find my signature vaguely amusing, please give Kudos.
KIfp67uy-Sr
Proud to be a Super User!PBI-Super-User-Rank-30x30-1x

View solution in original post

KNP
Super User
Super User

Glad I could help.

M is the language used in Power Query, DAX is everything to do with creating measures, calculated columns, tables.

uTRYSnkLGA.png

 

hN5XrxMLb1.png

 

If this is solved please accept solution on one of my answers above. 

 

Have I solved your problem?
Please click Accept as Solution so I don't keep coming back to this post, oh yeah, others may find it useful also ;).
chrome-9xf-Zagzel-B

If you found this post helpful, please give Kudos.
It gives me a sense of instant gratification and, if you give me Kudos enough times, magical unicorns will appear on your screen.
If you find my signature vaguely amusing, please give Kudos.
KIfp67uy-Sr
Proud to be a Super User!PBI-Super-User-Rank-30x30-1x

View solution in original post

5 REPLIES 5
KNP
Super User
Super User

Glad I could help.

M is the language used in Power Query, DAX is everything to do with creating measures, calculated columns, tables.

uTRYSnkLGA.png

 

hN5XrxMLb1.png

 

If this is solved please accept solution on one of my answers above. 

 

Have I solved your problem?
Please click Accept as Solution so I don't keep coming back to this post, oh yeah, others may find it useful also ;).
chrome-9xf-Zagzel-B

If you found this post helpful, please give Kudos.
It gives me a sense of instant gratification and, if you give me Kudos enough times, magical unicorns will appear on your screen.
If you find my signature vaguely amusing, please give Kudos.
KIfp67uy-Sr
Proud to be a Super User!PBI-Super-User-Rank-30x30-1x
KNP
Super User
Super User

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) 

 

 

Have I solved your problem?
Please click Accept as Solution so I don't keep coming back to this post, oh yeah, others may find it useful also ;).
chrome-9xf-Zagzel-B

If you found this post helpful, please give Kudos.
It gives me a sense of instant gratification and, if you give me Kudos enough times, magical unicorns will appear on your screen.
If you find my signature vaguely amusing, please give Kudos.
KIfp67uy-Sr
Proud to be a Super User!PBI-Super-User-Rank-30x30-1x

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

 

Have I solved your problem?
Please click Accept as Solution so I don't keep coming back to this post, oh yeah, others may find it useful also ;).
chrome-9xf-Zagzel-B

If you found this post helpful, please give Kudos.
It gives me a sense of instant gratification and, if you give me Kudos enough times, magical unicorns will appear on your screen.
If you find my signature vaguely amusing, please give Kudos.
KIfp67uy-Sr
Proud to be a Super User!PBI-Super-User-Rank-30x30-1x

@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

Helpful resources

Announcements
Fabcon_Europe_Social_Bogo

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.