Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Solved! Go to Solution.
Hi @KLee1024
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"
Hi @KLee1024
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"
That's a little bit complicated... I'm a beginner in Power Query.... is there any more easier way for me to understand..?
Hi @KLee1024
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"
It seems more easier, thank you!
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.