Power BI convert both HH:MM:SS format and decimal duration format to MM:SS format

1
votes

PLease review the screenshot.

enter image description here

The column contains both HH:MM:SS format and decimal duration in hours format with extra leading zeros. They are both in Text format.

How to covert them to MM:SS format? Like for example 01:28:10 should be 88:10

I'm quite confused by how to achieve that by using DAX.

I have tried multiple possible solutions but none worked.

1
powerbidaxdata-cleaningpowerbi-desktopdata-transform

1 Answers

1
votes

You may use the following function to only convert the format if it is in HH:MM:SS:

New Format = IF(len(Sheet1[ACD Time])=8,
            FORMAT(VALUE(LEFT(Sheet1[ACD Time],2))*60 + VALUE(MID(Sheet1[ACD Time],4,2)),"General Number")&":"&RIGHT(Sheet1[ACD Time],2),
                Sheet1[ACD Time])

Reference:

Format function https://dax.guide/format/

enter image description here