Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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!
Solved! Go to 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
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
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.
See if this works @Anonymous
This is the original data:
It transformed to this:
Here is what I did:
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.
DAX is for Analysis. Power Query is for Data Modeling
Proud to be a Super User!
MCSA: BI ReportingHi @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 @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. 😁
DAX is for Analysis. Power Query is for Data Modeling
Proud to be a Super User!
MCSA: BI ReportingCovering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
100 | |
51 | |
19 | |
12 | |
11 |