Forum Discussion

1001's avatar
1001
Resolver II
2 years ago
Solved

Converting .csv time duration format in Power Query

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, ...
Need Help
  • 1001's avatar
    1001
    2 years ago

    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.

     

dufoq3's avatar
dufoq3
Community Champion
2 years ago

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's avatar
    1001
    Resolver II
    2 years ago

    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's avatar
      dufoq3
      Community Champion
      2 years ago

      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