How do I use Power query to merge data from multiple tables on one sheet to a single table on another sheet?

[Fred56]

I have the following set up on sheet 1:

1 item Item Description
2 xxxx xxxxxxxxxxxxxxxxxx
3
4 Object1 Object2 Object 2
5 ABC1 CBA1
6 ABC2
7
8 Item Item Description
9 xxxx xxxxxxxxxxxxxxxx
10
11 Object1 Object 2 Object 3
12 ABC9 DDD1 CBA2
13 ABC2

What I have is a group of rows that represent a specific item and inside that group is a table of object with a varied number of rows. Different object Tables may contain the same objects. Note, this is just a partial example - there are actually around 200 different items represented on sheet 1 (therefore 200 object tables). What I want to do is merge all of the individual tables on sheet 1 into a single table on sheet 2 eg.

1 Object 1 Object 2 Object 3
2 ABC1 DDD1 CBA1
3 ABC2 CBA2
4 ABC9


Preferably, I would like to remove duplicates - in this case on sheet 2 the object 1 column is only showing 1 ABC1 and not 2 but if this ins't possible I could live with the duplicates. Basically what I want on sheet 2 is a master of list of all objects referenced in tables on sheet 1. I would like this to be dynamic so that if I add/edit/delete an object in a object table on sheet 1, that change is automatically reflected in the table on sheet 2.

Is there a way to do this using Power Query?

Thanks,
Fred

 

[scottsen]

Yep, Power Query can do this. You basically create 1 query for each of the tables, then a new query to "Append" them together (there is a ribbon button for the Append operation). And yes, you can remove dupes as well.