Forum Discussion
Currency Conversion
Hi Power BI Users, I have a set of data (image) that include multiple currency, all formatted as "Text". What could be the best way to convert them into USD? Data is on monthly basis, the desire rep...
Thank you. Please also provide a sample of your FX table for the currencies mentioned.
Here is the Power Query code to ingest your fact data
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("Tc4xDoMwDIXhq1jMlmU7CXbWFtpKIISoOiHuf41CClHH/1veW9fmeZtBnNRaZG42/Ekw/89IynyBkGZBSdB/lhPEA7pXyNFQY00NGU1rOnk6xmAepwKRjBNmhfvrcYKmYw66YSgQjInLA3j3O21f", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Current Value" = _t]), #"Added Custom" = Table.AddColumn(Source, "Value", each let s = Text.Split([Current Value]," ") in if Text.Start([Current Value],1)>"9" then s{1} else s{0}), #"Added Custom1" = Table.AddColumn(#"Added Custom", "Currency", each let s = Text.Split([Current Value]," ") in if Text.Start([Current Value],1)>"9" then s{0} else s{1}), #"Changed Type with Locale" = Table.TransformColumnTypes(#"Added Custom1", {{"Value", type number}}, "de-DE") in #"Changed Type with Locale"
Here is the sample data. I hope it's clear enough.
| Current Value | Expected Value | |
| GBP 18.276,00 | 18,276.00 | GBP |
| GBP 378,00 | 378.00 | GBP |
| GBP 4.200,00 | 4,200.00 | GBP |
| 1.291,15 EUR | 1,291.15 | EUR |
| 1.183,88 EUR | 1,183.88 | EUR |
| 947,24 EUR | 947.24 | EUR |
| 239,72 EUR | 239.72 | EUR |
| 8.856,00 PLN | 8,856.00 | PLN |
| 4.705,92 CHF | 4,705.92 | CHF |
| 4.250,00 DKK | 4,250.00 | DKK |
| 370.000,00 SEK | 370,000.00 | SEK |
Thank you. Please also provide a sample of your FX table for the currencies mentioned.
Here is the Power Query code to ingest your fact data
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("Tc4xDoMwDIXhq1jMlmU7CXbWFtpKIISoOiHuf41CClHH/1veW9fmeZtBnNRaZG42/Ekw/89IynyBkGZBSdB/lhPEA7pXyNFQY00NGU1rOnk6xmAepwKRjBNmhfvrcYKmYw66YSgQjInLA3j3O21f", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Current Value" = _t]),
#"Added Custom" = Table.AddColumn(Source, "Value", each let s = Text.Split([Current Value]," ") in if Text.Start([Current Value],1)>"9" then s{1} else s{0}),
#"Added Custom1" = Table.AddColumn(#"Added Custom", "Currency", each let s = Text.Split([Current Value]," ") in if Text.Start([Current Value],1)>"9" then s{0} else s{1}),
#"Changed Type with Locale" = Table.TransformColumnTypes(#"Added Custom1", {{"Value", type number}}, "de-DE")
in
#"Changed Type with Locale"Hi, I only use the "Change Type with Locale" of your suggestion. For the other steps, I added custom columns with "Text.select" and "Text.remove" query. But I suppose your code will do the same tricks. It works charmly now. Thank you very much