Converting .csv time duration format in Power Query

[1001]

Hi All, I have a csv file with the following format below ]H]mm:ss, but the Query Editor is treating this as text. How best can I convert this to hh.mm.ss? When I try to convert it to anything else, I get a column error. Just wondering if someone might be able to assist. Thanks.

[1001 · Accepted solution]

Hi Guys, we actually found three solutions with the first option being our choice. These were:

1)  Use the source software to output the Duration time in Total Minutes as opposed to [h]:mm:ss. Once imported to the Query Editor, create a custom column converting the minutes to hours by dividing by 60.  

2)  Use the Query Editor and M Code below in a Custom Column to convert the tex to numbers in decimal hours.

Number.FromText (Text.Middle([Used capacity],0,2))+

Number.FromText (Text.Middle([Used capacity],3,2))/60+

Number.FromText (Text.Middle([Used capacity],6,2))/3600)

3)  import the csv file to Excel where we would convert the tex to decimal hours to then import to PBI's Query editor.  The Excel formula would be =LEFT(D4,2)+(MID(D4,6,2)/60)+(RIGHT(D4,2)/3600)

All three solution gives us the final deimal hours. Guys I sincerely appreciate your solutions and will keep them handy for the future. Many thanks. Cheers.

[v-junyant-msft]

Hi @1001 ,

In Power BI, if you have more than 24 hours, then you can't set it to render as hh:mm:ss under the time type. But you can set it to text type and then use DAX to set it to be presented as hh:mm:ss.
First of all, I would recommend that you convert all the data in the source data to units of seconds before importing it into Power BI, or you can transform it in Power Query.
Then you can use this DAX to create a measure:

Total Duration = 
VAR TotalSeconds = SUM('Table'[Total Seconds])
VAR Hours = INT(TotalSeconds / 3600)
VAR Minutes = INT(MOD(TotalSeconds, 3600) / 60)
VAR Seconds = MOD(TotalSeconds, 60)
RETURN FORMAT(Hours, "0") & ":" & FORMAT(Minutes, "00") & ":" & FORMAT(Seconds, "00")

The final output is as below:

You can refer to my response in this thread:
Solved: Summing time that has more than 24 hours in a form... - Microsoft Fabric Community

Best Regards,
Dino Tao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[dufoq3]

Hi @1001,

duration in Power Query is d:hh:mm:ss

Before:

After:

If you don't want to transform all columns, you can specify columns for transformation i.e.:
List.Transform( { "Used capacity, "Free capacity" } ), 

Just find this comment in my code and you can replace it.

let
    Source = #table(type table[Used capacity = text, Free capacity = text], {{"97:59:06", "9:30:53"}, {"75:00:39", "32:29:20"}}),
    TransformToDuration = 
      Table.TransformColumns(
          Source,
          List.Transform(Table.ColumnNames(Source), //if you don't want to transform all columns, you can specify here columns for transformation i.e.:  List.Transform( { "Used capacity, "Free capacity" } ),
              (colName)=> { colName, each 
                            [ split = List.Transform(Text.Split(_, ":"), Number.From) ,
                              duration = #duration(0, split{0}, split{1}, split{2})
                            ][duration], type duration } 
          )
      )
in
    TransformToDuration

[1001]

Hi Dufoq3, thanks for providing this answer. Unfortunately, I need to keep durations displayed to at least hh:mm.  Am thinking of importing the csv file to Excel and reformat the durations to a decimal number.

Kind thanks. 

[dufoq3]

Hi @1001, if you want to have it in hours as decimal number:

Result:

let
    Source = #table(type table[Used capacity = text, Free capacity = text], {{"97:59:06", "9:30:53"}, {"75:00:39", "32:29:20"}}),
    TransformToDuration = 
      Table.TransformColumns(
          Source,
          List.Transform(Table.ColumnNames(Source), //if you don't want to transform all columns, you can specify here columns for transformation i.e.:  List.Transform( { "Used capacity, "Free capacity" } ),
              (colName)=> { colName, each 
                            [ split = List.Transform(Text.Split(_, ":"), Number.From) ,
                              hoursNumber = (split{0} * 3600 + split{1} * 60 + split{2}) / 3600
                            ][hoursNumber], type number } 
          )
      )
in
    TransformToDuration

[tharunkumarRTK]

@1001 
# of hours in your column is more than 23, I think this is the reason why you are not able to convert your column into 'Time' datatype. If you want to convert them to Time data type then I would suggest you split the number days and remainging hours into two different columns. 

If the post helps please give a thumbs up

If it solves your issue, please accept it as the solution to help the other members find it more quickly.

Tharun