Forum Discussion

Anonymous

Not applicable

4 years ago

Solved

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...

Need Help

lbendlin

4 years ago

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"

lbendlin

Super User

4 years ago

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.

Anonymous

Not applicable

4 years ago

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

Super User

4 years ago

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"

Anonymous
Not applicable
4 years ago
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