How to merge multiple tables (5) with a common column

Question

How can I merge multiple tables, similar to Excel - How to merge rows from 2 tables based on a common column?

But I have about 5 different data sets with a semi common column - i.e. say for 800 rows in a sheet, 600 of them are common, whilst the remaining are unique.

I am wanting to show where that data item is used in the adjacet columns which are imported in from the other sheets.

I have used a PowerQuery to combine all the sheets into one, but this causes the common column to duplicate e.g.:

Below you can see what it initially looks like, and what the desired output would be.

Any suggestions would be great!

Answer 1

Use Power Query to unpivot the columns.

This option is on the Transform Ribbon of Power Query. Highlight all columns except Column A, then click Unpivot Columns. The result will be three columns:

Column 1 - Unique Column, listing Data 1, Data 2, etc.

Column 2 - Attribute, listing Area 1, Area 2, etc.

Column 3 - Value, listing Used, Not Used, etc.

Once you unpivot, the next step is to close and load the query into a worksheet. This gives you a table, from which you should be able to create a pivot table that looks like the result you want.

Once this is set up, you only have to add data to the last row of your new table (the one loaded from Power Query), then click refresh on your pivot table to update the report.