Power Query M Language-Convert Seconds to HH:MM:SS Format in Power BI

M-language-seconds - HHMMSS

Table of Contents

Power Query M Language-Convert Seconds to HH:MM:SS Format in Power BI

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”

Seconds to HH:MM:SS In Power BI
 

Step 2: Go to Add Column Tab and create a custom column  and write the M Script Code #duration(0,0,0,[Seconds])

 
M Script code for converting Seconds to HH:MM:SS


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.

Categories
quick-report
Power BI Desktop
Kaushlendra Mishra

How to create a report quickly in Power BI Service?

Do you want to create a quick report in Power BI? The latest Power BI version has made it possible to create a quick report using data from the web and existing published datasets in Power BI Service. So, now you do need to download the Power BI Desktop software into your PC and directly make a report using the Power BI service.

Read More »
WHAT IS AVAILABILITY SET & AVAILABILITY ZONE
Azure
Kaushlendra Mishra

What is Availability Set and Availability Zone in Azure?

What is Availability Set and Availability Zone in Azure? In Availability Set, VM`s copy is created and is isolated with separate physical servers, computer racks, storage units, network switches in a data center within a region so if one of the VM stops working then still we have the copy of the VM working within the same datacenter. While Availability Zone ensures that complete datacenter data is replicated to another place at a distance within the same region so that even if complete data center fails due to any reason then also Availability of data is ensured.

Read More »
prepare-DP-200-exam
Azure
Kaushlendra Mishra

How to Prepare for DP-200 Exam?

I have passed the DP-200 exam on 28 Oct 2020. I have gone through the various study material and courses in order to prepare for the exam. Overall, the time taken by me for the DP-200 exam is approx 3 months. Ideally, you should not take 3 months of time but due to lack of guidance, I happen to take a number of online courses which was not required to be taken for passing the DP-200 Exam.

Read More »

5 thoughts on “Power Query M Language-Convert Seconds to HH:MM:SS Format in Power BI”

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

  2. 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!

Leave a Comment

Your email address will not be published. Required fields are marked *