cancel
Showing results for
Did you mean:

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

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.

1 ACCEPTED SOLUTION
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.

6 REPLIES 6
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.

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:

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.

Super User

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``````

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

Resolver II

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.

Super User

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``````

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

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

Announcements

#### New forum boards available in Real-Time Intelligence.

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

#### Power BI Monthly Update - May 2024

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

#### Fabric certifications survey

Certification feedback opportunity for the community.

Top Solution Authors
Top Kudoed Authors