Convert to Time Format in the Power Query Editor in Power BI while importing the data from Excel

[mrk777]

I have some data in an EXCEL TABLE which has total hours worked and actual work hours and which resembles the Duration in the form of %s as per the below-mentioned screenshot:

Now, I would use this Excel file in POWER BI and showcase the same duration and %s. But unfortunately, In PowerBI, the data type is being considered as Text, and hence unable to convert them to the formats as per Excel file. If I try to change it to Date or Date/Time, Decimal, or Duration, it is throwing an error. I need to perform calculations like the Sum of all the Durations etc.,

Following is one type of error when I converted to Duration Data Type, likewise, it is showing an error for all the data types I tried.

Appreciate your help, thank you!

[ppm1]

Here is another approach. You can add a custom column with this expression which will get you as a decimal in days. You can then use the approach in the linked article to do your measures and then format as durations.

= let
splitlist = List.Transform(Text.Split([YourColumn], ":"), each Number.From(_))
in
splitlist{0}/24 + splitlist{1}/(24*60) + splitlist{2}/(24*60*60)

Calculate and Format Durations in DAX – Hoosier BI

Pat

[ronrsnfld]

See this answer to your identical question at Convert to Time Format in the Power Query Editor in Power BI while importing the data from Excel 

[tackytechtom · Accepted solution]

Hi @mrk777 ,

I'd suggest to convert your duration column into seconds. With that you should be able to do all your calculations. You might wanna do something like the below:

And here the M code for the new column:

Number.FromText ( 
    Text.BeforeDelimiter([duration],":") 
    ) * 60 * 60
+ Number.FromText ( 
    Text.BetweenDelimiters([duration], ":", ":" ) 
    ) * 60
+ Number.FromText ( 
    Text.AfterDelimiter([duration], ":", {0, RelativePosition.FromEnd})  
    )

I used the example above in this blog post with a little bit more explanations:
https://www.tackytech.blog/how-to-swiftly-take-over-power-query/#11_Calculate_duration_from_time_col...

Let me know if this helps 🙂

/Tom
https://www.tackytech.blog/
https://www.instagram.com/tackytechtom/