Transform table for currency conversion from any currency to any currency

[Anonymous]

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

Date	Transaction Currency	Report Currency	Exchange Rate
01/01/2020	GBP	EUR	1.1
01/01/2020	USD	EUR	0.9
01/02/2020	GBP	EUR	1.05
01/02/2020	USD	EUR	0.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.

Date	Transaction Currency	Report Currency	Exchange Rate
01/01/2020	GBP	EUR	1.1
01/01/2020	USD	EUR	0.9
01/01/2020	EUR	GBP	0.91
01/01/2020	EUR	USD	1.11
01/01/2020	GBP	GBP	1
01/01/2020	USD	USD	1
01/01/2020	GBP	USD	1.22
01/01/2020	USD	GBP	0.82
01/02/2020	GBP	EUR	1.05
01/02/2020	USD	EUR	0.85
01/02/2020	EUR	GBP	0.95
01/02/2020	EUR	USD	1.18
01/02/2020	GBP	EUR	1
01/02/2020	USD	USD	1
01/02/2020	GBP	USD	1.24
01/02/2020	USD	GBP	0.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!

[JW_van_Holst · Accepted solution]

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]

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.

[edhans]

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]

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]

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