Power Query M Language-Convert Seconds to HH:MM:SS Format in Power BI
I got a scenario to convert the Seconds to HH:MM:SS in Power BI.
Step 1: Click on Edit Query and go to the query having seconds in the Column. Below is the screenshot of the Query. The table is “TimeTable” and Column having second Value is “Seconds”
Step 2: Go to Add Column Tab and create a custom column and write the M Script Code #duration(0,0,0,[Seconds])
Step 3: You will be getting values like the below screenshot in the new custom column.
Step 4: Close the Power Query and come to Data Tab in Power BI Desktop. You will now see the values in Time Column in Decimal Format.
Step 5: In Data Tab, Go to Modelling Tab and change the Data type of Time Column to “Time” and format to HH:MM: SS.
As per the above screenshot, You have successfully converted the Seconds to HH:MM: SS time format.
You can also change the Seconds to HH:MM: SS time format using DAX using simple mathematical calculation but that process is a little complex than the process mentioned above.
Please do comment if you find any error in this article or able to find any other way to achieve the same result.
Thank you.
Any ideas on how to handle this when the number of hours exceeds 24? This method wouldn't account for that.
my query has multiple columns, one of the columns contain seconds which i want to convert to hh:mm:ss
how to do this?
You can achieve your requirement through steps mentioned in this article. If seconds are given in a column then easily you can create a seperate column having value in hh:mm:ss format.
Please follow the article.
But you can't sum a Time field? Or am I missing something?
Question: First of all, this is great! Thank you! Thankfully, if it's longer than 24 hours, it turns the excess into days (d.hh:mm:ss). Is there a way to change the hours of the day so that if it's at x amount of hours it turns to a day? I'm wanting to change once it gets to 12 hours so for example if it was 13 hours, it'd look like 1.01:00:00. Thank you!
Any ideas on how to handle this when the number of hours exceeds 24 This method wouldn Skidson
before converting hh:mm:ss does related column needs to do format . like : decimal , whole number , time etc
Aw, this was an extremely nice post. Taking the time and actual effort to generate a very good article… but what can I say… I procrastinate a whole lot and don’t manage to get nearly anything done.
Hi, I do believe this is a great web site. I stumbledupon it 😉 I will come back yet again since I book-marked it. Money and freedom is the greatest way to change, may you be rich and continue to help other people.