Power Query does not match all records merging multiple worksheets RRS feed

  • Question

  • Hi guys,
    I need to consolidate several Tables, one in each WorkSheet of a single Workbook, into one master table using PowerQuery but PowerQuery doesn’t match all the records.
    Please let me explain:

    1- I have a table with 2 fields: Customer Name and VAT. 600 records.
    2- I have 20 tables in 20 Worksheets (one for each), each table refers to one Supplier and lists the turnovers of each customer that purchased from that supplier (surely not all the 600 names from the first table). The fields for each table are the following:
    a- VAT number (as in Table 1)
    b- Customer Name (as in table 1)
    c- Supplier 1 YTD September 2015
    d- Supplier 1YTD September 2014
    e- Supplier 1 Variation %

    So, the last three fields (c-d-e) will be different for each supplier in each one of the 20 sheets. I know I could use the same name for those three fields in each sheet, maybe adding an extra-column for Supplier Name, but I don’t need the consolidation table to be summarized by a Pivot. I need the table to run horizontally, showing those three fields for each supplier name.
    In other words, the final table should have all the customer names and VAT for each customer name taken from the first table (600 records) and 20 “block” of those three fields (c-d-e) for each Supplier. In case some customers do not have any purchase from any of the suppliers, the corresponding cell should show a 0.
    In order to achieve that, I used PowerQuery, but once I start to Merge the first table with one of the 20s, the procedure shows that query was able to match only a small portion of all the records in table 1 (let’ say only 275 out of 600) and I am unable to understand why and how to fix that.
    I properly formatted all the tables.

    I am enclosing a file with an example of the situation.

    The worksheet "Final" is the way the table should come out after consolidation of all the other worksheets.

    As you may see, the Final Sheet shows turnovers of ALL customers, even those not shown in any (or all) the turnover reports in the other Sheets. Can you please help me? Thanks a lot

    Test File

    Sunday, October 18, 2015 7:16 AM