Forum Discussion

mrk777's avatar
mrk777
Frequent Visitor
3 years ago
Solved

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

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 wou...
Need Help
tackytechtom's avatar
tackytechtom
Most Valuable Professional
3 years ago

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_column

 

 

Let me know if this helps 🙂

 

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