Extract data after - in power bi

[Anonymous]

Hello All,

I have table having data like below.

USA-West

USA-East

I need to extract text after -

Please suggest how to do

[themistoklis]

@Anonymous 

If there are names without a dash then you can add a handling error function

Column = IFERROR(MID(Sheet1[Name], FIND("-",Sheet1[Name])+1,300), Sheet1[Name])

[TomasAndersson]

Hi! 

You can use a combination of SEARCH(), MID() and LEN().

Column = 
MID(
    'Table'[Name],
     SEARCH("-",'Table'[Name]) + 1,
     LEN('Table'[Name]) - SEARCH("-",'Table'[Name])
     )

Hope this helps!

[Anonymous]

Hi @TomasAndersson ,

I am getting below error.

[TomasAndersson]

Not seing the specific error but could be that you have some rows that do not have "-" which causes SEARCH() to throw an error. You can adjust the calculated column to this then:

Column = 
MID(
    'Table'[Name],
     SEARCH("-",'Table'[Name],1,0) + 1,
     LEN('Table'[Name]) - SEARCH("-",'Table'[Name],1,0)
     )

[Anonymous]

Hi @TomasAndersson ,

Thanks for your answer.

I need to check one more condition here.

If my column having values like below and I need to show like this

if Column = 'SupportL2 – (ESI-India)' then ESI-India
if Column = 'SupportL2 – APAC (ESI-China)' then ESI-China
if Column = 'SupportL2 – APAC (ESI-PCS)' then ESI-PCS

and if the column having values apart from this I need use the logic we created above.

Please suggest how to do

[TomasAndersson · Accepted solution]

You'd have to use IF() and then add a search for something that is unique for those rows. I assumed "(" is unique for these and did below, but you might have to adjust accordingly:

 

Column = 
IF(
    SEARCH("(",'Table'[Name],1,-1) > 0,

    MID(
        'Table'[Name],
        SEARCH("(",'Table'[Name]) + 1,
        LEN('Table'[Name]) - SEARCH("(",'Table'[Name]) - 1
    ),

    MID(
        'Table'[Name],
        SEARCH("-",'Table'[Name],1,0) + 1,
        LEN('Table'[Name]) - SEARCH("-",'Table'[Name],1,0)
        )
)

Hope this helps!


Did my answer help? Feel free to give kudos and mark as solution to show your support. Thanks!