cancel
Showing results for
Did you mean:

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a \$100 discount. Register Now

Helper I

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
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 ;).

 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. Proud to be a Super User!
Super User

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.

 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 ;).

 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. Proud to be a Super User!
5 REPLIES 5
Super User

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.

 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 ;).

 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. Proud to be a 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 ;).

 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. Proud to be a Super User!
Helper I

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

Super User

Firstly, just for clarification, this is Power Query not DAX.

The #duration function has #duration(days, hours, minutes, seconds).

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 ;).

 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. Proud to be a Super User!
Helper I

@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

Announcements

#### Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

#### Power BI Monthly Update - February 2024

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

#### Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

#### Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.

Top Solution Authors
Top Kudoed Authors