Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Reply
1001
Resolver II
Resolver II

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, I get a column error. Just wondering if someone might be able to assist. Thanks.

 

1001_0-1709113826618.png

 

1001_1-1709113986148.png

 

 

1 ACCEPTED SOLUTION
1001
Resolver II
Resolver II

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.

 

View solution in original post

6 REPLIES 6
1001
Resolver II
Resolver II

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
Community Support
Community Support

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:

vjunyantmsft_0-1709193561852.png

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
Super User
Super User

Hi @1001,

 

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

 

Before:

dufoq3_0-1709136849712.png

 

After:

dufoq3_1-1709136871221.png

 

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

Note: Check this link to learn how to use my query.
Check this link if you don't know how to provide sample data.

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. 

 

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

 

Result:

dufoq3_0-1709195062131.png

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

Note: Check this link to learn how to use my query.
Check this link if you don't know how to provide sample data.

tharunkumarRTK
Solution Sage
Solution Sage

@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

 

Helpful resources

Announcements
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

MayPowerBICarousel

Power BI Monthly Update - May 2024

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

LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.

Top Solution Authors
Top Kudoed Authors