locked
Merging Tables with multiple matches RRS feed

  • Question

  • Hi there, 

    I am trying to create a report using Power Query instead of Ablebits Merge Two Tables. I am merging two tables based on customer number and product number in order to show if bookings are filled with invoices. What is happening currently is all invoices with the customer and product number that match to the booking number's customer and product number are being pulled in, meaning invoices are filling more than one booking. Below is a basic example of what is happening and what I want to happen. I have tried creating a new column with invoice number and product number concatenated but then it removes booking numbers that I need. 

    Example:

    Booking #       Cust #        Product #                                Invoice #        Cust #       Product #

    1                     A                   A1                                         100                 A               A1

    2                     A                   A1                                         200                 A               A1

    What is currently happening:

    Booking #           Cust #            Product #          Invoice #

    1                           A                   A1                    100

    2                           A                   A1                    100

    2                           A                   A1                    200

    What needs to happen:

    Booking #           Cust #            Product #          Invoice #

    1                           A                   A1                    100

    2                           A                   A1                    200


    Wednesday, April 10, 2019 3:56 PM

Answers

All replies

  • Without any unique column or columns to match, how do you determine which booking belongs to which invoice? Is all of your customer and invoice data lined up as nicely as you show in the example so that you just match by position? 

    Based on your sample output, you can simply remove duplicates based on Customer, Product, and Invoice numbers. You would first need to buffer your output table before removing duplicates to ensure the proper order of duplicate removal (i.e. to ensure that booking 1 with invoice 100 turns up in the result instead of booking 2 with invoice 100).

    I'd be curious to know how Ablebits resolves this issue.

    Wednesday, April 10, 2019 5:12 PM
  • Hi there, 

    I am using the customer number and product number to match the booking # with the invoice #. Unfortunately there is a lot of data so matching by position wouldn't be possible either. 

    I have tried creating a unique column based on invoice number and customer number and removing duplicates, but what happens is that it removes booking numbers that I need since the invoice was matched to multiple booking numbers. 

    Thank you!

    Thursday, April 11, 2019 1:47 PM
  • I am using the customer number and product number to match the booking # with the invoice #

    So what criterion or criteria are you using to guarantee that the records match correctly without a unique key in any table, and without a chronological order for the records in the tables (with suggests that for any customer and product, invoice 200 could appear before invoice 100, or booking 2 could show up before booking 1)?

    Could you explain how booking numbers are assigned?


    Thursday, April 11, 2019 3:00 PM
  • Under certain conditions, using a nested index to create the merge-columns could be the solution here:  (on customer-index and customer-invoice#) - base

    https://social.technet.microsoft.com/Forums/en-US/47ae9305-22e9-4220-8038-e5960697f6ca/group-by-create-index-within-groups?forum=powerquery

    But of course, that depends on how your data is structured. 

    Apart from that: As Colin said.


    Imke Feldmann - MVP Data Platform (PowerBI) - http://www.TheBIccountant.com 

    Please use the 'Mark as answer' link to mark a post that answers your question. If you find a reply helpful, please remember to vote it as helpful - Thanks!

    Saturday, April 13, 2019 6:26 AM