Changing multiple row names

[Sambidha]

I have a table as below: 

Country	Cost	Percentage
Africa pacific region	1k	95%
Asia Region	2k	96%
Europe Region	3k	97%
Latin America Region	4k	98%

Here i want to change the country column fileds as like below : 

Country	Cost	Percentage
AFR	1k	95%
ASR	2k	96%
EUR	3k	97%
LAR	4k	98%

Can someone please with this.

Thanks in advance.

[Icey · Accepted solution]

Hi @Sambidha ,

You can try to create a calculated column like so:

Column =
VAR LastWord =
    TRIM (
        RIGHT (
            SUBSTITUTE ( [Country], " ", REPT ( " ", LEN ( [Country] ) ) ),
            LEN ( [Country] )
        )
    )
RETURN
    UPPER ( LEFT ( [Country], 2 ) & LEFT ( LastWord, 1 ) )

Best regards

Icey

If this post helps,then consider Accepting it as the solution to help other members find it faster.

[Daviejoe]

Let me know if this helps (also, be mindful that Replace Values is case sensitive)

[Daviejoe]

There are a couple ways of doing this.

If you go into Power Query, in the Transform tab, replace values and replace the longer names entirely with the abbreviations.

Or you create a table that has the current names with a new column added to house the abbreviations and then you have this as a linked Dimension table.

[Sambidha]

Hi,

Can you please help me with the query.

Thanks.

[Daviejoe]

Of course, give me five mins and I'll have a step through ready for you