How to insert a new column by specific word

[Anonymous]

I have a column, product name with currency, like this

AAA05 - AAAAAA 5 Year plan USD

AAA10 - AAAAAA 10 Year plan USD

BBB10-X - B&B 200 10 Year Plan CNY - Series III

CCCC15 - CCCC DDDD 15 Year Plan GBP

EEEEE - Single Premium EEEEEEE Select EUR

---------------------------------------------------------------------

Then, how can I use above data to select currency unit as a new column? Below is example:

                    Product Name                                         |                 Currency

AAA05 - AAAAAA 5 Year plan USD                             |                    USD

AAA10 - AAAAAA 10 Year plan USD                           |                    USD

BBB10-X - B&B 200 10 Year Plan CNY - Series III       |                     CNY

CCCC15 - CCCC DDDD 15 Year Plan GBP                   |                     GBP

EEEEE - Single Premium EEEEEEE Select EUR              |                     EUR

[Vera_33]

Hi @Anonymous 

If Product Name only contains on Currency, here is one way, I have a CurrencyList to contain all the Currency

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("ZY7BCsIwEER/ZcjZwqYQ70kbJBcJloKl5lAkSCEtEu3/m0RQwXca2DfDjiOTUpJABVmAwOCniHuYVvRdy9yuKJy+Ssr/jlKKU3VOlrpsRPVeoSb6uDa7zXFI987H2T9gjCnFJsHzAzmgTYCLn9JB2eLpTK7P6y142OiXeVug36TV4K9P6P7EnHsB", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Product Name" = _t]),
    CurrencyList = {"USD","CNY","GBP","EUR"},
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Product Name", type text}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", (x)=> List.Select( CurrencyList, each Text.Contains(x[Product Name],_)){0}?)
in
    #"Added Custom"

[Anonymous]

That's a little bit complicated... I'm a beginner in Power Query.... is there any more easier way for me to understand..?

[Vera_33 · Accepted solution]

Hi @Anonymous 

It's not that difficult, basically just use List function, you can also write custom function like switch, or a more manual way - if you have lots of values in Currency list, it is very long...add a custom column, paste that if statement (if you have more than 3 if, you need to consider changing the way)

if Text.Contains([Product Name], "USD") then "USD"
else if Text.Contains([Product Name], "CNY") then "CNY"
else if Text.Contains([Product Name], "GBP") then "GBP"
else if Text.Contains([Product Name], "EUR") then "EUR"
else "Unknown"

[Anonymous]

It seems more easier, thank you!