Need to find and replace bulk words from one column

[nagaraj007]

Hello All,

I am struck with a problem wherein i have a COMPANY COLUMN wherein there are old and new names, i need to replace old name with new name and to keep others as it is. Please advice how to go about this. I searched a lot in Forums but i am really sorry i didnt get the idea. Please find the attached list.

[v-lid-msft]

Hi @nagaraj007 ,

We can create a calculated column using following formula to meet your requirement:

ReplaceWith = 
VAR companyName = [Company Name]
VAR length =
    LEN ( companyName )
VAR t =
    FILTER (
        'Table',
        LEN ( [Company Name] ) > length
            && LEFT ( 'Table'[Company Name], length ) = companyName
    )
RETURN
    IF ( COUNTROWS ( t ) > 1, MAXX ( t, [Company Name] ), companyName )

BTW, pbix as attached.

Best regards,

Community Support Team _ Dong Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[nagaraj007 · Accepted solution]

Thank you very much for your help. 

[v-lid-msft]

Hi @nagaraj007 ,

Sorry for my mistake, we should use the >=1 in formula.

ReplaceWith = 
VAR companyName = [Company Name]
VAR length =
    LEN ( companyName )
VAR t =
    FILTER (
        'Table',
        LEN ( [Company Name] ) > length
            && LEFT ( 'Table'[Company Name], length ) = companyName
    )
RETURN
     IF ( COUNTROWS ( t ) >= 1, MAXX ( t, [Company Name] ), companyName )

Best regards,

Community Support Team _ Dong Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.