Currency Conversion

[manhuynguyen]

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 report outlook would be: Country - Month - Amount (USD). Thank you

[manhuynguyen]

Hi, 

Thank you for your response. I will use the exchange rate DIM table provided internally by my company. What I'm stucking at is that

1) The data come by text form, e.g: "999 EUR". And some are mixing in format, e.g: "999 EUR" vs. "GBP 999", which makes delimited column by space not possible. 

2) The number format is also not good: it's "123.456,78" instead of "123,456.78" so my PBI does not recognize them at numbers.

What would you suggest to solve these issues?

Thanks.

[lbendlin]

Please provide sanitized sample data that fully covers your issue. If you paste the data into a table in your post or use one of the file services it will be easier to work with. Avoid posting screenshots of your source data if possible.

Please show the expected outcome based on the sample data you provided. Screenshots of the expected outcome are ok.

[manhuynguyen]

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

[lbendlin · Accepted solution]

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"

[manhuynguyen]

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

[lbendlin]

Which exchange rate service are you planning to use? Does that service have an API?  What type of rate do you need?   Please provide a more detailed explanation of what you are aiming to achieve. What have you tried and where are you stuck?