Excel how to join separate tables

Question

I am working with a collection of tables (in different workbooks) that share the same structure. These are the job registers for a given year, and the primary key for each item is the year (2018) plus the 4 digit 'row number' (1, 2, 3...), so we have IDs that look like '20180001', '20170191', etc.

My issue is that I need to be able to perform a vlookup on these values without knowing which sheet the number will be in, meaning they all need to be on the same sheet. I have experimented with copy pasting the IDs all into a single table, and then doing another vlookup there to pull the data into that table so that I could reference it as the master copy, but this doesn't allow expansion at all, and must be manually handled at all times. I've tried some stuff with PowerQuery, but I can't figure out how to append multiple queries together.

Here is an example of what the tables might look like.

This is an example of what the outcome might look like.

Note how table 1 with only 3 columns has an extra column without data in it, so that it matches the size of table 2.

Each table is nearly identical in structure, meaning they have mostly the same columns (each year, we might add another column or two to the current register), with the only difference being the tuples.

How might I be able to append these tables together dynamically so that it can refresh whenever needed and keep accurate data on hand?

Answer 1

Do you want to get the result as below: