cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
New Member

Power Query Merge with multiple criteria

I have two tables, Sales and Payment. Both table have four columns. Sample is shown below: 

Sales

Transaction DateAmountBankStore
2/1/20203886AANC
2/1/20201163AANC
2/1/20203879AANC
2/1/20207058AANC
2/1/2020688AANC
2/1/20203965AANC
2/1/202016988AANC
2/1/2020175AANC
2/1/20205018AANC
2/1/20201771AANC

Payment

Transaction DateAmountBankStore
2/1/202025733.45AANC
2/1/2020839AANC
2/1/20202827.78AANC
2/1/2020646AANC
2/1/20201942AANC
2/1/20201913.72AANC
2/1/20205064AANC
2/1/20201450AANC

I want to merge both tables, based on following 11 criteria

----------------------------

1 Match sales sheet Amount column with Payment sheet amount column

For Unmatch Transactions

2 Than subtract -1 in unmatch transactions and match with payment sheet amount column

3 Than subtract -2 in unmatch transactions and match with payment sheet amount column

4 Than subtract -3 in unmatch transactions and match with payment sheet amount column

5 Than subtract -4 in unmatch transactions and match with payment sheet amount column

6 Than subtract -5 in unmatch transactions and match with payment sheet amount column

7 Than subtract +1 in unmatch transactions and match with payment sheet amount column

8 Than subtract +2 in unmatch transactions and match with payment sheet amount column

9 Than subtract +3 in unmatch transactions and match with payment sheet amount column

10 Than subtract +4 in unmatch transactions and match with payment sheet amount column

11 Than subtract +5 in unmatch transactions and match with payment sheet amount column

----------------------------

3 REPLIES 3
Super User II
Super User II

Hi @Sohail-Merchant 

 

Would you mind to produce a table with the expected result?

 

Best Regards,
Mariusz

If this post helps, then please consider Accepting it as the solution.

Please feel free to connect with me.
LinkedIn

 

Single Separate table, satisfying all the condition is perfect. However, you can provide any other solutions to get the merge data according to the mention criteria.

it looks like you're looking for something like this:

 

= Table.SelectRows(sales, each List.Contains(payment[Amount],_[Amount],(x,y)=>Number.Abs(x-y)<=5))

 


but it is only an attempt to interpret what you have written, because it is not clear at all to me.

 

if you want a possibly quick and useful answer, it would be preferable for you to provide examples of  input and expected output.

Helpful resources

Announcements
secondImage

Happy New Year from Power BI

This is a must watch for a message from Power BI!

December Update

Check it Out!

Click here to read more about the December 2020 Updates!

Community Blog

Check it Out!

Click here to read the latest blog and learn more about contributing to the Power BI blog!

Get Ready for Power BI Dev Camp

Get Ready for Power BI Dev Camp

Mark your calendars and join us for our next Power BI Dev Camp!.

Top Solution Authors
Top Kudoed Authors