Creating a Relationship with duplicate values in power Query in Excel

[geemian93]

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

[dufoq3 · Accepted solution]

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"

[AlienSx · Accepted solution]

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

[dufoq3 · Accepted solution]

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"