Struggling to split column or extract data - zero values

[Anonymous55]

Hi!

I have an Excel spreadsheet which contains a list of phone calls received, and the durations of those calls. The durations are displayed as HH:MM:SS, for example:

00:00:21

00:00:13

00:01:06

00:02:06

I would like to remove the starting '00:' from the data, as the phone calls will never exceed 59 minutes so the '00:' isn't needed (and is making my visual difficult to read). 

I have tried splitting the column by delimiter, number of characters, and position... but Power BI is disregarding some 0s in each cell and producing strange results like:

00:                          0

00:                          0

00:                          1

00:                          2

I have also tried extracting data from the column by characters, range and delimiters, but get similarly strange results. I even tried adding a column from examples, but this doesn't work either.

I would like the data to look like this:

00:21

00:13

01:06

02:06

Is there any way to achieve this in Power BI (possibly need to use DAX)? Or will I need to change this in my original Excel file?

Thank you!

[Anonymous55]

Thank you so, so much for your help @rsbin and apologies for my late reply! I have just tried your solution and it's worked perfectly. Thank you again for your help, I would never have figured this out on my own 🙂

[rsbin · Accepted solution]

@Anonymous55 ,

In Power Query:

Text.End( [Duration],5 )  -- Replace [Duration] with the name of your column

In DAX

NewColumn = RIGHT( [Duration], 5 )