Forum Discussion

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

 

Need Help
  • lbendlin's avatar
    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"

6 Replies

  • lbendlin's avatar
    lbendlin
    Super User
    4 years ago

    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?

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

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

        Here is the sample data. I hope it's clear enough.

        Current ValueExpected Value
        GBP 18.276,00       18,276.00GBP
        GBP 378,00             378.00GBP
        GBP 4.200,00          4,200.00GBP
        1.291,15 EUR          1,291.15EUR
        1.183,88 EUR          1,183.88EUR
        947,24 EUR             947.24EUR
        239,72 EUR             239.72EUR
        8.856,00 PLN          8,856.00PLN
        4.705,92 CHF          4,705.92CHF
        4.250,00 DKK          4,250.00DKK
        370.000,00 SEK     370,000.00SEK