Skip to main content
cancel
Showing results for 
Search instead 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

Reply
misunika95
Frequent Visitor

Duration conversion issues

Hi Everyone!

 

I have some data that reflects how long a person took to resolve a dispute. The format is hh:mm (for example 1328:45) and when I try to change the data type to duration the following error appears in Power Query:

 

Expression.Error: We couldn't parse the Duration literal.
Details:
1328:45

 

Does anyone know how to fix this problem? Thanks in advance guys 🙂

1 ACCEPTED SOLUTION
artemus
Employee
Employee

You can use the following custom step:

= Table.TransformColumns(PreviousStep,{{"ColumnName", each let parsed = Text.Split(_, ":") in #duration(0, 0, Int64.From(parsed{0}), Int8.From(parsed{1})), type duration}})

Just change PreviousStep and "ColumnName" to appropiate values.

View solution in original post

12 REPLIES 12
artemus
Employee
Employee

You can use the following custom step:

= Table.TransformColumns(PreviousStep,{{"ColumnName", each let parsed = Text.Split(_, ":") in #duration(0, 0, Int64.From(parsed{0}), Int8.From(parsed{1})), type duration}})

Just change PreviousStep and "ColumnName" to appropiate values.

Anonymous
Not applicable

Hello @artemus 

I wanted to convert the column (tot_loggedhours) from text to duration to total seconds, in order to get the total duration.

do you know how to fix the below errors?


Please help me thanks.

 

--

Paul

Conversion.PNGExpression Error.PNG

The number of hours is over 24, meaning it expects a day parameter like 1.2:30:45.

 

Just split the column by :, then convert each part to number, multiply the minutes and hours, and finallly add them all up

Just to clarify to other PBI users. I insert this code as a custom step where I would have changed the data type to Duration. And it worked perfectly for me. Thank you.

Anonymous
Not applicable

Hi @artemus ,

 

Thank you for the solution, it helped fix a similar issue i had with my numbers. I do have a quick question about the logic.

 

So, using your formula, i see a cell which was previously text(hh:mm:ss) 1594:44:28 changes to a duration of 1.02:34:44 (ddd.hh:mm:ss) post applying this logic. I am a bit confused, i was expecting to see around 66 days instead of the 1.02 like above.

 

It'd greatly help if you can explain the logic of the custom step. 

 

Thanks for your time.

 

-K

The formula I gave only works for mm:ss, not hh:mm:ss. It treated your 1594:44:28 as 1594:44

 

If you had that format you could use:

#duration(0, Int64.From(parsed{0}), Int8.From(parsed{1}, Int8.From(parsed{2})

 

This would only work if you always had the hour component (event if it was 0). If you wanted to support both formats would you need to:

 

if parsed{2}? = null then #duration(0, 0, Int64.From(parsed{0}), Int8.From(parsed{2})) else #duration(0, Int64.From(parsed{0}), Int8.From(parsed{1}), Int8.From(parsed{2}))

 

 

Anonymous
Not applicable

Thanks for taking the time to reply. I am running into this error at the else part of your code. 

 

I am pretty new to M code, can you please help correct any syntax issues i might have overlooked?

 

= Table.TransformColumns(#"Reordered Columns",{{"Time to Accept", each let parsed = Text.Split(_, ":") in if parsed{2}? = null then #duration(0, 0, Int64.From(parsed{0}), Int8.From(parsed{2}) else #duration(0, Int64.From(parsed{0}), Int8.From(parsed{1}, Int8.From(parsed{2}), type duration}})

 

Error -

 

Thaks again for your time.

 
 

pbi_pquery_delete.png

I have the same SyntaxError. Did you have the solution?

The formula has been corrected.

Thank you for the information. Is there any way to have the Duration data type to diaplay the format as hh:nn:ss rather than d.hh:nn:ss?

It worked, thanks a lot! Do you happen to know how can I display the data? At the visualization level I get very low number values.

Thank you!!


@artemus wrote:

You can use the following custom step:

= Table.TransformColumns(PreviousStep,{{"ColumnName", each let parsed = Text.Split(_, ":") in #duration(0, 0, Int64.From(parsed{0}), Int8.From(parsed{1})), type duration}})

Just change PreviousStep and "ColumnName" to appropiate values.


 

As part of your final step you can change the column type back to text. Note that if you do this, it will display it in [dd.][hh:][mm:]ss format (as seen in the previewer.

 

However, if you do this it will not sort correctly in the visualizer. To get around this, duplicate the duration column and then change the one you want to display to text. After loading it into the report view, select the text field, then select Modeling from the top ribbon and Sort by Column and choose the duration column that wasn't changed to text.

Helpful resources

Announcements
Fabric Community Conference

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.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

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

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

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