cancel
Showing results for
Did you mean:
Frequent Visitor

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

 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!

1 ACCEPTED SOLUTION
Resolver III

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),
#"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}}),
Custom1 = BaseTable&InvertedTable&NonEuroCurrTable&OwnCurrExchangeRate,
FinalResult = Table.TransformColumnTypes(Custom1,{{"Exchange Rate", Currency.Type}})
in
FinalResult``````
5 REPLIES 5
Resolver III

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),
#"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}}),
Custom1 = BaseTable&InvertedTable&NonEuroCurrTable&OwnCurrExchangeRate,
FinalResult = Table.TransformColumnTypes(Custom1,{{"Exchange Rate", Currency.Type}})
in
FinalResult``````
Frequent Visitor

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.

Super User III

See if this works @crustyoreo
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

Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling

Proud to be a Super User!

MCSA: BI Reporting
Frequent Visitor

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?

Super User III

I would have to think about that a bit @crustyoreo 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. 😁

Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling

Proud to be a Super User!

MCSA: BI Reporting

Announcements