Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

Transform table for currency conversion from any currency to any currency

Hi all,

 

I'm currently working on a dynamic currency conversion for a dashboard based on this tutorial: https://www.kasperonbi.com/currency-conversion-in-dax-for-power-bi-and-ssas/

 

However, I don't have a table that shows the exchange rate for each date from any currency to any currency but rather a table for each date from any currency to only one currency (Table 1).

 

DateTransaction CurrencyReport CurrencyExchange Rate
01/01/2020GBPEUR1.1
01/01/2020USDEUR0.9
01/02/2020GBPEUR1.05
01/02/2020USDEUR0.85

Table 1 Origin Table

 

Does anyone know how to transform Table 1 within Power Query Editor so it resembles Table 2? In the end, I would like to have a table that has the exchange rate for any month from any currency to any other currency.

 

DateTransaction CurrencyReport CurrencyExchange Rate
01/01/2020GBPEUR1.1
01/01/2020USDEUR0.9
01/01/2020EURGBP0.91
01/01/2020EURUSD1.11
01/01/2020GBPGBP1
01/01/2020USDUSD1
01/01/2020GBPUSD1.22
01/01/2020USDGBP0.82
01/02/2020GBPEUR1.05
01/02/2020USDEUR0.85
01/02/2020EURGBP0.95
01/02/2020EURUSD1.18
01/02/2020GBPEUR1
01/02/2020USDUSD1
01/02/2020GBPUSD1.24
01/02/2020USDGBP0.81

Table 2 Desired Table (example values)

 

I already thought about creating a matrix and then unpivoting the matrix again, but I am also not sure about how to create the matrix in Power Query.

 

Thanks!

Need Help
  • JW_van_Holst's avatar
    JW_van_Holst
    5 years ago

    Here you are (month and days in European style but I trust you can cope with that ;-))

    The code has become quite complex but I saw no way around this. 
    Outline of the solution:

    - append of 4 tables:

    - Basetable (the input table);
    - InverteredTable (the inverse of the input table)

    - NonEurCurrTable (a table with the other currency than EUR)

    - Owncurrency (always exchange rate 1).

    Good luck,

    //JW

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjDUByIjAyMDJR0ld6cAIOkaGgQkDfUMlWJ10BSEBrvAFRjoWcIVGGE3wcAUUwWqERZAFbEA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Date = _t, #"Transaction Currency" = _t, #"Report Currency" = _t, #"Exchange Rate" = _t]),
    BaseTable = Table.TransformColumnTypes(Source, {{"Date", type date},{"Exchange Rate", type number}}, "en-US"),
    TClist = List.Distinct(BaseTable[#"Transaction Currency"]),
    DateColumn = Table.Distinct(Table.SelectColumns(BaseTable,{"Date"})),
    InvertedTable = Table.RenameColumns(Table.RemoveColumns(Table.AddColumn(Table.AddColumn(Table.AddColumn(BaseTable, "TC", each [Report Currency], type text), "RC", each [Transaction Currency], type text), "ER", each 1/[Exchange Rate], type number),{"Transaction Currency", "Report Currency", "Exchange Rate"}),{{"TC", "Transaction Currency"}, {"RC", "Report Currency"}, {"ER", "Exchange Rate"}}),
    #"Expanded Custom.1" = Table.ExpandListColumn(Table.AddColumn(Table.ExpandListColumn(Table.AddColumn(DateColumn, "Custom", each TClist), "Custom"), "Custom.1", each TClist), "Custom.1"),
    #"Added Custom2" = Table.AddColumn(#"Expanded Custom.1", "Custom.2", each if [Custom]=[Custom.1] then true else false),
    #"Filtered Rows" = Table.SelectRows(#"Added Custom2", each ([Custom.2] = false)),
    #"Removed Columns" = Table.RemoveColumns(#"Filtered Rows",{"Custom.2"}),
    #"Added Custom3" = Table.AddColumn(#"Removed Columns", "Custom.2", (OT)=> Table.SelectRows(BaseTable, (IT)=> OT[Date] = IT[Date] and OT[Custom] = IT[Transaction Currency])[Exchange Rate]{0}, type number),
    #"Added Custom4" = Table.AddColumn(#"Added Custom3", "Custom.3", (OT)=> Table.SelectRows(BaseTable, (IT)=> OT[Date] = IT[Date] and OT[Custom.1] = IT[Transaction Currency])[Exchange Rate]{0}, type number),
    #"Added Custom5" = Table.AddColumn(#"Added Custom4", "Custom.4", each [Custom.2] / [Custom.3], type number),
    #"Removed Columns1" = Table.RemoveColumns(#"Added Custom5",{"Custom.2", "Custom.3"}),
    #"Renamed Columns" = Table.RenameColumns(#"Removed Columns1",{{"Custom", "Transaction Currency"}, {"Custom.1", "Report Currency"}, {"Custom.4", "Exchange Rate"}}),
    NonEuroCurrTable = Table.TransformColumnTypes(#"Renamed Columns",{{"Transaction Currency", type text}, {"Report Currency", type text}}),
    TableWithoutRightHeaders = Table.AddColumn(Table.AddColumn(Table.ExpandListColumn(Table.AddColumn(DateColumn, "Custom", each List.Combine({TClist,{"EUR"}})), "Custom"), "Custom.1", each [Custom]), "Custom.2", each 1),
    OwnCurrExchangeRate = Table.RenameColumns(TableWithoutRightHeaders, List.Zip({Table.ColumnNames(TableWithoutRightHeaders), Table.ColumnNames(NonEuroCurrTable)}) ),
    Custom1 = BaseTable&InvertedTable&NonEuroCurrTable&OwnCurrExchangeRate,
    FinalResult = Table.TransformColumnTypes(Custom1,{{"Exchange Rate", Currency.Type}})
in
    FinalResult

5 Replies

  • edhans's avatar
    edhans
    Icon for Community Champion rankCommunity Champion
    5 years ago

    See if this works Anonymous 
    This is the original data:

    It transformed to this:

    Here is what I did:

    1. Added an opposite currency rate - which is simply the 1/currency rate 
    2. Removed the actual currency rate
    3. Swapped the names of the report/transaction currency and renamed the Opposite Exch. rate to just Exchange rate
    4. Appended that with the original table from the #"Changed Type" step.

    Full M code is here:

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjDUByIjAyMDJR0ld6cAIOkaGgQkDfUMlWJ10BSEBrvAFRjoWcIVGGE3wcAUUwWqERZAFbEA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Date = _t, #"Transaction Currency" = _t, #"Report Currency" = _t, #"Exchange Rate" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type date}, {"Exchange Rate", Currency.Type}}),
    #"Added Opposite Currency Rate" = Table.AddColumn(#"Changed Type", "Opposite Exchange Rate", each Number.Round(1/[Exchange Rate],4), Currency.Type),
    #"Removed Other Columns" = Table.SelectColumns(#"Added Opposite Currency Rate",{"Date", "Transaction Currency", "Report Currency", "Opposite Exchange Rate"}),
    #"Renamed Columns" = Table.RenameColumns(#"Removed Other Columns",{{"Transaction Currency", "Report Currency"}, {"Report Currency", "Transaction Currency"}, {"Opposite Exchange Rate", "Exchange Rate"}}),
    #"Appended Query" = Table.Combine({#"Renamed Columns", #"Changed Type"})
in
    #"Appended Query"

    How to use M code provided in a blank query:
    1) In Power Query, select New Source, then Blank Query
    2) On the Home ribbon, select "Advanced Editor" button
    3) Remove everything you see, then paste the M code I've given you in that box.
    4) Press Done
    5) See this article if you need help using this M code in your model.

     

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      5 years ago

      Hi edhans, 

       

      thanks a lot for your help!

       

      I was able to implement all the steps. However, the table is missing the exchange rate pairs between the different transaction currencies (in this case GBP to USD and USD to GBP).

       

      This one is a bit trickier, since it would need to pick up the exchange rate for EUR-other currency to make the calculation ( GBP-EUR/EUR-USD). Any idea on how to get these pairs, too? 

      • edhans's avatar
        edhans
        Icon for Community Champion rankCommunity Champion
        5 years ago

        I would have to think about that a bit Anonymous but I am not sure that is a valid path. Just becuase you know the EUR to USD translation and the EUR to HKD, you don't have the exact USD to HKD. In my financial models I would be reluctant to interpolate rates not expressly given like that. There are people in the markets that make money on those types of spreads. 😁

  • JW_van_Holst's avatar
    JW_van_Holst
    Icon for Resolver IV rankResolver IV
    5 years ago

    Here you are (month and days in European style but I trust you can cope with that ;-))

    The code has become quite complex but I saw no way around this. 
    Outline of the solution:

    - append of 4 tables:

    - Basetable (the input table);
    - InverteredTable (the inverse of the input table)

    - NonEurCurrTable (a table with the other currency than EUR)

    - Owncurrency (always exchange rate 1).

    Good luck,

    //JW

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjDUByIjAyMDJR0ld6cAIOkaGgQkDfUMlWJ10BSEBrvAFRjoWcIVGGE3wcAUUwWqERZAFbEA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Date = _t, #"Transaction Currency" = _t, #"Report Currency" = _t, #"Exchange Rate" = _t]),
    BaseTable = Table.TransformColumnTypes(Source, {{"Date", type date},{"Exchange Rate", type number}}, "en-US"),
    TClist = List.Distinct(BaseTable[#"Transaction Currency"]),
    DateColumn = Table.Distinct(Table.SelectColumns(BaseTable,{"Date"})),
    InvertedTable = Table.RenameColumns(Table.RemoveColumns(Table.AddColumn(Table.AddColumn(Table.AddColumn(BaseTable, "TC", each [Report Currency], type text), "RC", each [Transaction Currency], type text), "ER", each 1/[Exchange Rate], type number),{"Transaction Currency", "Report Currency", "Exchange Rate"}),{{"TC", "Transaction Currency"}, {"RC", "Report Currency"}, {"ER", "Exchange Rate"}}),
    #"Expanded Custom.1" = Table.ExpandListColumn(Table.AddColumn(Table.ExpandListColumn(Table.AddColumn(DateColumn, "Custom", each TClist), "Custom"), "Custom.1", each TClist), "Custom.1"),
    #"Added Custom2" = Table.AddColumn(#"Expanded Custom.1", "Custom.2", each if [Custom]=[Custom.1] then true else false),
    #"Filtered Rows" = Table.SelectRows(#"Added Custom2", each ([Custom.2] = false)),
    #"Removed Columns" = Table.RemoveColumns(#"Filtered Rows",{"Custom.2"}),
    #"Added Custom3" = Table.AddColumn(#"Removed Columns", "Custom.2", (OT)=> Table.SelectRows(BaseTable, (IT)=> OT[Date] = IT[Date] and OT[Custom] = IT[Transaction Currency])[Exchange Rate]{0}, type number),
    #"Added Custom4" = Table.AddColumn(#"Added Custom3", "Custom.3", (OT)=> Table.SelectRows(BaseTable, (IT)=> OT[Date] = IT[Date] and OT[Custom.1] = IT[Transaction Currency])[Exchange Rate]{0}, type number),
    #"Added Custom5" = Table.AddColumn(#"Added Custom4", "Custom.4", each [Custom.2] / [Custom.3], type number),
    #"Removed Columns1" = Table.RemoveColumns(#"Added Custom5",{"Custom.2", "Custom.3"}),
    #"Renamed Columns" = Table.RenameColumns(#"Removed Columns1",{{"Custom", "Transaction Currency"}, {"Custom.1", "Report Currency"}, {"Custom.4", "Exchange Rate"}}),
    NonEuroCurrTable = Table.TransformColumnTypes(#"Renamed Columns",{{"Transaction Currency", type text}, {"Report Currency", type text}}),
    TableWithoutRightHeaders = Table.AddColumn(Table.AddColumn(Table.ExpandListColumn(Table.AddColumn(DateColumn, "Custom", each List.Combine({TClist,{"EUR"}})), "Custom"), "Custom.1", each [Custom]), "Custom.2", each 1),
    OwnCurrExchangeRate = Table.RenameColumns(TableWithoutRightHeaders, List.Zip({Table.ColumnNames(TableWithoutRightHeaders), Table.ColumnNames(NonEuroCurrTable)}) ),
    Custom1 = BaseTable&InvertedTable&NonEuroCurrTable&OwnCurrExchangeRate,
    FinalResult = Table.TransformColumnTypes(Custom1,{{"Exchange Rate", Currency.Type}})
in
    FinalResult
    • Anonymous's avatar
      Anonymous
      Not applicable
      5 years ago

      Thanks, this actually seemed to work! However, the query was more than 5 GB large so I ended up creating a matrix in Excel and then unpivoted it in the query editor.