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.

     

1001's avatar
1001
Resolver II
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.