Forum Discussion
How to insert a new column by specific word
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
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"
4 Replies
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"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"