3
0
Hi everyone, this time I have come up with another interesting post where we will be looking at how to convert time column in hh:mm:ss format into seconds.
I recently got a scenario where I had a column with a time value in hh:mm:ss and I had to convert it into seconds so that I can perform other calculations. So I will be explaining this with two different ways:
1. By using Power Query [M language]
2. By creating New Column
- Using Power Query [M language]
The Value column is the time column in hh:mm:ss format which needs to be converted into seconds. To achieve this, select the Transform data in the Home tab from Power BI Desktop home page. You will come to Power Query.
Go to Add Column tab, and select the Custom Column option. A new dialog box will appear where we can write our query using the M language.
Just like other programming languages, the M language also have various predefined functions and we will be using some of them to achieve our goal.
The image above shows the Custom Column dialog box where on the top, you have to specify the new column name, on the right-hand side, you will see all the columns associated with the table so that you can use it and on the left-hand side, some area is provided to write the custom column formula.
I have used Time.Minute() and Time.Second() function to get the desired outcome.
Time.Minute([Value])*60 + Time.Second([Value])In my case, I have not converted the hour part. If you need to change the hour part as well then use the Time.Hour() function and multiply it with 3600 (1 hour = 3600 seconds).
Time.Hour([Value])*3600 + Time.Minute([Value])*60 + Time.Second([Value])Click OK when you have correctly written the condition. I get the result with the column name Threshold_In_Second as shown in the below screenshot.
2. Creating A New Column
The above process can also be achieved by adding a new column. You just have to use some DAX functions.
Seconds =
VAR A =
TIMEVALUE(<Table_Name>[Column_Name])
VAR B = (A - TRUNC(A))
* 86400
RETURN
B
I have used TIMEVALUE() and
TRUNC() function to get the results.
Hope, this will be helpful. Thank you.
Follow me on Twitter, Instagram, and connect with me on LinkedIn. For consulting, mail me.
Amazing! Really helpful! 🙂
Thank you.
How should i proceed if i want to do it otherwise. To convert seconds to HH:mm:ss ?