Power Query does not match all records merging multiple worksheets

[cydmm]

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:
   1. VAT number (as in Table 1)
   2. Customer Name (as in table 1)
   3. Supplier 1 YTD September 2015
   4. Supplier 1 YTD September 2014
   5. Supplier 1 Variation %

So, the last three fields (3-4-5) 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. Can you please help me? Thanks a lot

 

[HolyExcel]

tough without seeing the data, but you could try to clean the fields where VAT is stored of trailing or leading spaces

 

[cydmm]

tough without seeing the data, but you could try to clean the fields where VAT is stored of trailing or leading spaces

Hi HolyExcel,
fields where VAT is stored are cleaned up and properly formatted already.
I would like to attach a sample file but I am unable to find a button on the control panel here aimed to this purpose.
How can I attach here a sample file with several sheets?

 

[cydmm]

Here it is a sample file. I strongly hope someone will help me

http://www.filedropper.com/testpowerquery

 

[ImkeF]

Just fill in all your Supplier tables in the first step: ...

let
Source = Table.Combine({Supplier1,Supplier2...}),
UnpivotOther = Table.UnpivotOtherColumns(Source, {"VAT", "Customers"}, "Attribute", "Value"),
PivotBack = Table.Pivot(UnpivotOther, List.Distinct(UnpivotOther[Attribute]), "Attribute", "Value")
in
PivotBack

 

[cydmm]

Just fill in all your Supplier tables in the first step: ...

let
Source = Table.Combine({Supplier1,Supplier2...}),
UnpivotOther = Table.UnpivotOtherColumns(Source, {"VAT", "Customers"}, "Attribute", "Value"),
PivotBack = Table.Pivot(UnpivotOther, List.Distinct(UnpivotOther[Attribute]), "Attribute", "Value")
in
PivotBack

Hi Imke,
Thanks for your answer but I am a beginner about PowerQuery so would You please be so Kind To provide me with further details about The procedure I should follow And where To put The formula You suggested?
Thanks in advance

 

[ImkeF]

Hi,
bit in a hurry: Doing is easier than describing - uploaded your file here: https://www.dropbox.com/s/c3cyzbjzgmzwqby/ME_AppendPivotStyle.xlsx?dl=0

Cons1 contains the query according to the way I suggested. You would need to push all your tables to Power Query like I did with your first 2 tables. Then just type them in.
Technique is described here: Append Multiple Tables in Power Query | The White Pages

There’s another query “Cons2” that does that fully automatic (consolidating all tables in the workbook whose names start with "Supplier") using advanced techniques that I might blog about sometime.
Part of the techniques used is described here: How to auto-parametrize your Power Query queries – The BIccountant

You need to enable Fast combine: Power Query -> Settings -> Options -> Privacy -> Ignore Privacy Levels…