Hello all you fine geniuses. Hope you are having a wonderful day as you read this.

I have a question about a Connected Table (Power Query).

Long version:

A long time ago I made a form to compare two sheets of data and remove/add the matching or no longer matching rows, and it would also keep all the notes and data after a certain column. This allowed for a type of customer database that could always be updated, because we don't have good ERP/CRM at our office.
A user here (xladept, have't seen in awhile, hope he/she is doing well..was super helpful) helped me right the macro that combines the sheets. It has always worked well but it has been causing issues due to other reasons (namely slow computers the office uses and the file having 4,000 rows often)

So I am rewriting it with a Power Query connection(s) instead.

What I have so far is dataset that gets imported in here and there, so it changes (some rows may be added, some may be deleted, as eluded to above). It's customer data so it contains account numbers, company names, phone number, Etc.
In the output table I have added a few columns the user can use as notes for that customer.

Now, sometimes customers will be removed in one of the updates, this of course will break the notes and now all the notes will be aligned with the wrong row.

My current solution is that I have the initial dataset that has the connection which creates a new table, I added the notes columns to this new table. I then created another PQ query from this output table and I do a connection only and use relationships and generate another output table.
This works, but it causes me to have to have three different datasets with 4,000 rows each, so it (presumably) can bog stuff down.

IUs this the best way to do this or is there a better more elegant way? I am working on a sample file that I can add to this post but was hoping that would not be necessary.


Short Version (TL:DR)

I have a dataset that I run in PQ to create the desired output data. I added several columns to the output Table to add notes to that row. When updating the original dataset and rows are deleted, the notes are no longer lined up.
I am seeking the easiest and cleanest solution to keep the notes with their original row.

Current Solution: Creating a PQ from original dataset, then creating a new connection from the new table including the notes rows, and outputting that to a final table that is used. It's messy though and makes the files large since it ends up creating three sheets of the same data.

Curious if there is a different solution for this.


Thanks in advance!