Forum Discussion
Creating a Relationship with duplicate values in power Query in Excel
Hello There,
I have two tables, 1. Transaction Table and 2. Exchange Rate Table as below.
The transaction table records the daily transaction in multiple currecnies while the exchange rate table contain the daily exchange rates.
I wanted to convert the amounts from trsasaction table multipled by the exchange rates as per the currency column and as per date but both table consist multple transactions and exchange rates at multiple dates it doesn't allow to craete relationship. I had tried creating "Bridge Table" for Date and Currency Code, however it still doesn't work.
Transaction Table:
DateDoc No.AmountTrans. Currency
| 1/1/2023 | 1000025 | 1250 | USD |
| 5/1/2023 | 1000030 | 200 | USD |
| 9/1/2023 | 1000050 | 100 | USD |
| 10/1/2023 | 1000100 | 350 | USD |
Exchange Rate Table:
Daterep. CurrencyUSD/EUR Rate
| 1/1/2023 | EUR | 0.95000 |
| 1/2/2023 | EUR | 0.95125 |
| 1/3/2023 | EUR | 0.92654 |
| 1/4/2023 | EUR | 0.95784 |
| 1/5/2023 | EUR | 0.95789 |
| 1/6/2023 | EUR | 0.95700 |
| 1/7/2023 | EUR | 0.95450 |
| 1/8/2023 | EUR | 0.94975 |
| 1/9/2023 | EUR | 0.95251 |
| 1/10/2023 | EUR | 0.95400 |
| So on: | --- | --- |
The desired results should be as follows:
DateDoc No. AmountCurrencyUSD/EUR RateEUR Amount
| 1/1/2023 | 1000025 | 1250 | USD | 0.95000 | 1187.5 |
| 5/1/2023 | 1000030 | 200 | USD | 0.95789 | 191.58 |
| 9/1/2023 | 1000050 | 100 | USD | 0.95251 | 95.25 |
| 10/1/2023 | 1000100 | 350 | USD | 0.95400 | 333.90 |
Hi geemian93, with your's sample data it is simple (but you have probably different data)
Result
let TransactionTable = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMtQ31DcyMDJW0lEyNAACI1MQy8jUAEiFBrsoxepEK5miqTEGSRoZICuxRFMC1m+IosTQAFUNRNYYYVMsAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [DateDoc = _t, #"No." = _t, #"AmountTrans." = _t, Currency = _t]), ExchRateTable = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("bc7BDcAgCAXQXTibCghaBugCTXoy7r9Ga4K94IXLy//83oEyZUYukOB67u/iYYqIMNJEjkisjiUgVxVHicl2LtQdmmPd4D+oRRRdeAYUa2utxSQrORJueufT8QI=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Daterep." = _t, Currency = _t, #"CurrencyUSD/EUR Rate" = _t]), TransactionTableChangedType = Table.TransformColumnTypes(TransactionTable,{{"No.", Int64.Type}, {"AmountTrans.", type number}, {"DateDoc", type date}}, "sk-SK"), ExchRateTableChangedType = Table.TransformColumnTypes(ExchRateTable,{{"Daterep.", type date}, {"CurrencyUSD/EUR Rate", type number}}, "en-US"), MergedQueries = Table.NestedJoin(TransactionTableChangedType, {"DateDoc"}, ExchRateTableChangedType, {"Daterep."}, "ExchRateTableChangedType", JoinKind.LeftOuter), #"Expanded ExchRateTableChangedType" = Table.ExpandTableColumn(MergedQueries, "ExchRateTableChangedType", {"CurrencyUSD/EUR Rate"}, {"CurrencyUSD/EUR Rate"}) in #"Expanded ExchRateTableChangedType"Make sample tables with multi currency transactions and rates together with resulting table. Now it works for single ccy pair.
let combine = transactions & Table.RenameColumns(rates, {"Daterep.", "Date"}), sort = Table.Sort(combine,{{"Date", Order.Ascending}, {"Doc No.", Order.Ascending}}), rate_down = Table.FillDown(sort,{"USD/EUR Rate"}), filter = Table.SelectRows(rate_down, each ([Currency] = "USD")), amount = Table.AddColumn(filter, "EUR Amount", each [#"AmountTrans."] * [#"USD/EUR Rate"]) in amount
2 Replies
Hi geemian93, with your's sample data it is simple (but you have probably different data)
Result
let TransactionTable = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMtQ31DcyMDJW0lEyNAACI1MQy8jUAEiFBrsoxepEK5miqTEGSRoZICuxRFMC1m+IosTQAFUNRNYYYVMsAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [DateDoc = _t, #"No." = _t, #"AmountTrans." = _t, Currency = _t]), ExchRateTable = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("bc7BDcAgCAXQXTibCghaBugCTXoy7r9Ga4K94IXLy//83oEyZUYukOB67u/iYYqIMNJEjkisjiUgVxVHicl2LtQdmmPd4D+oRRRdeAYUa2utxSQrORJueufT8QI=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Daterep." = _t, Currency = _t, #"CurrencyUSD/EUR Rate" = _t]), TransactionTableChangedType = Table.TransformColumnTypes(TransactionTable,{{"No.", Int64.Type}, {"AmountTrans.", type number}, {"DateDoc", type date}}, "sk-SK"), ExchRateTableChangedType = Table.TransformColumnTypes(ExchRateTable,{{"Daterep.", type date}, {"CurrencyUSD/EUR Rate", type number}}, "en-US"), MergedQueries = Table.NestedJoin(TransactionTableChangedType, {"DateDoc"}, ExchRateTableChangedType, {"Daterep."}, "ExchRateTableChangedType", JoinKind.LeftOuter), #"Expanded ExchRateTableChangedType" = Table.ExpandTableColumn(MergedQueries, "ExchRateTableChangedType", {"CurrencyUSD/EUR Rate"}, {"CurrencyUSD/EUR Rate"}) in #"Expanded ExchRateTableChangedType"Make sample tables with multi currency transactions and rates together with resulting table. Now it works for single ccy pair.
let combine = transactions & Table.RenameColumns(rates, {"Daterep.", "Date"}), sort = Table.Sort(combine,{{"Date", Order.Ascending}, {"Doc No.", Order.Ascending}}), rate_down = Table.FillDown(sort,{"USD/EUR Rate"}), filter = Table.SelectRows(rate_down, each ([Currency] = "USD")), amount = Table.AddColumn(filter, "EUR Amount", each [#"AmountTrans."] * [#"USD/EUR Rate"]) in amount