cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
crustyoreo
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).

 

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!

1 ACCEPTED SOLUTION
JW_van_Holst
Resolver III
Resolver III

Capture.PNG

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

View solution in original post

5 REPLIES 5
JW_van_Holst
Resolver III
Resolver III

Capture.PNG

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

View solution in original post

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
Super User III
Super User III

See if this works @crustyoreo 
This is the original data:

edhans_0-1618532548986.png

It transformed to this:

edhans_1-1618532575486.png

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.

 

 



Did I answer your question? Mark my post as a solution!
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

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? 

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 I answer your question? Mark my post as a solution!
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

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group!

MBAS on Demand

Microsoft Business Applications Summit sessions

On-demand access to all the great content presented by the product teams and community members! #MSBizAppsSummit #CommunityRocks

MBAS Attendee Badge

Claim Your Badge & Digital Swag!

Check out how to claim yours today!

secondImage

Are You Ready?

Test your skills now with the Cloud Skills Challenge.

Top Solution Authors