Forum Discussion

misunika95's avatar
misunika95
Frequent Visitor
6 years ago
Solved

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...
Need Help
Show and Tell
Tips and Tricks
Tutorial Requests
  • artemus's avatar
    artemus
    6 years ago

    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.

misunika95's avatar
misunika95
Frequent Visitor
6 years ago

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.

 

artemus's avatar
artemus
Icon for Microsoft Employee rankMicrosoft Employee
6 years ago

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.