cancel
Showing results for
Did you mean:

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

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

#### New forum boards available in Real-Time Intelligence.

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

#### Power BI Monthly Update - May 2024

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

#### Fabric certifications survey

Certification feedback opportunity for the community.

Top Solution Authors
Top Kudoed Authors