PowerQuery problems

[CraigG]

Hi,

I have numerous workbooks which all have multiple worksheets but with the same names. Eg, 'Sheet1' is the same across all workbooks.
All workbooks have the same headings/columns, in the same order. One of the columns is called 'Reference_number:'

I can bring them all together in PowerQuery no problem, all rows are imported no issue.

I have another workbook, with different columns but with one common column - 'Reference_number:' - which matches the columns in the other workbooks.
I want to add this workbook to the others.

In PowerQuery I have two queries:
- Query1 - brings together all of the worksheets with the same columns
- Query2 - brings in the last workbook (where the columns are not the same as those in query1 except for the 'Reference_number:' column.

I've tried 'merging' and 'appending' using the 'Reference_number:' column as a match, but no matter what I do I can't get all rows included in the query so I can then use a PivotTable.
I'm joining using 'FullOuter'. Sometimes I receive the #NAME error.

An example, of what I'm looking for is:

WORKSHEET 1:
Reference_number: Description: Amount:
A01------------------Produce1----1.00

WORKSHEET 2:
Reference_number: Description: Amount:
A01------------------Produce55----99.00

WORKSHEET 3:
Reference_number: Live: Figures:
A01------------------Yes----7777777.21


In my query (where all workbooks are together) I want all columns to be included eg, Reference_number: / Description / Amount / Live / Figures with all rows.
So, in a PivotTable I could filter by Reference_number: and it give me the relevant rows.

I don't know what I'm doing wrong. Any help much appreciated.

 

[Vogateer]

So you're connecting the data using the Data Model with that Reference Number field? I don't use this feature, but I believe you can use that info with Power Pivot.