POWER QUERY - Merging 2 tables between workbooks

Objective:

Get data only from 1 column from Auxillary file into the Main file table.


Challenges:

The auxillary file is a software-generated xlsx from a database, which has more data than I need. Also comes as a range. Both files have a matching Product Number column. Based on that match, I want to get SKU data from Aux file into the Main file table. The SKU column does not currently exist in the main file. The Aux file will have more products than main table, but I am only interested in the ones listed in the main table.

Is there a way to add only the SKU values directly to the main table? Instead of having a 3rd table from the merge?


Also, a new file with recent information will be generated from time to time and saved into the same folder. Should I override existing file with the same name or save under a different name?

And, if someone moved, renamed, deleted the aux file by accident, will my merged data in main file be affected?

After doing a bit of research, I don't think it is possible for Power Query or Data Model to insert a column into an existing table. And I do admit that it most cases it would be just easiest to add merged data into new sheet.

But because this will be shared by other people and they will be adding manually data to the table aside from that column because that information isn't available to them.

I think the best solution will be 1) to use Power Query to Load to aux file into the main a a sheet. 2) Create a column in the main table and use a Lookup function.