Forum Discussion

Anonymous55's avatar
Anonymous55
Frequent Visitor
2 years ago
Solved

Struggling to split column or extract data - zero values

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 ,

    In Power Query:

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

    In DAX

    NewColumn = RIGHT( [Duration], 5 )

2 Replies

  • rsbin's avatar
    rsbin
    Community Champion

    Anonymous55 ,

    In Power Query:

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

    In DAX

    NewColumn = RIGHT( [Duration], 5 )
  • Anonymous55's avatar
    Anonymous55
    Frequent Visitor

    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 🙂