Convert hh:mm:ss to mm:ss either in Power Query Editor or in DAX

[KGBOR]

I have a hh:mm:ss: value coming in from a raw data and I'd like to convert that to mm:ss as the time value itself is below 1 hour so the "hh" part brings no value. How can I convert or remove that "hh" part? I found that in Power Query editor I can get out the minutes and second separately and might be able to combine them but that'd just put way too burden on the entire model.

Thank you!

[Anonymous · Accepted solution]

Hi @KGBOR ,

If the column is time type, you could try this formula:

Column = FORMAT('Table'[Column1],"nn:ss")

If the column is text type, you could try this formula:

Column = RIGHT('Table'[Column1],5)

Best Regards,

Jay

[mahoneypat]

Please see this article. The best way to handle durations is to keep them as a decimal, do your aggregations, and then format them at the end with "mm:ss".

Calculate and Format Durations in DAX – Hoosier BI

Pat

[amitchandak]

@KGBOR , new column in power query

#time(0,Time.Minute([TimeCol]) , Time.Seconds([TimeCol]) )

[KGBOR]

Thank you @amitchandak ! For some reason it still kept the hh part at the beginning. Is there any method for such conversion?