Forum Discussion

Sohail-Merchant's avatar
Sohail-Merchant
Regular Visitor
6 years ago

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

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

Need Help
Show and Tell

3 Replies

  • Mariusz's avatar
    Mariusz
    Community Champion
    6 years ago

    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

     

    • Sohail-Merchant's avatar
      Sohail-Merchant
      Regular Visitor
      6 years ago

      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.

      • Anonymous's avatar
        Anonymous
        Not applicable
        6 years ago

        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.