Manually Entered Data Shifting After Refresh

[ralphy32]

I'm using Power Query to pull data from 3 different tables within the same workbook. Once that data is collected, I'm putting it in a master list. This master list table has additional columns of data that can only be added after the master list is compiled, and these additional columns are manually entered (not using formulas of any kind).

The problem occurs when the master list is refreshed with new/additional data. The rows of PQ data shift, but the manually entered rows stay static. Is there a way to have the data within the Master List table move as one so that the PQ data and manual data don't become misaligned after a refresh of new data?

Here's a visualization of what I'm talking about with "auto" representing the PQ data and "Manual" representing data that is manually entered:

PQ1 PQ2 PQ3 Manual1 Manual2 Manual3
Auto Auto Auto Manual Manual Manual

 

[peter789]

Yes it's a brilliant technique to learn. Do a web search for self referencing queries and the answer should be there. Basically you add a unique reference (index) to each row in you original query. Create another query based on the table that is created from your original data with the manual data added then join it back to itself.

Peter

 

[ralphy32]

Yes it's a brilliant technique to learn. Do a web search for self referencing queries and the answer should be there. Basically you add a unique reference (index) to each row in you original query. Create another query based on the table that is created from your original data with the manual data added then join it back to itself.

Peter

Thanks so much for the info. I was certain the answer was out there, but I had no idea what to search for. All I needed was "Self-referencing query" and I was off to the races.

Here's the best answer I found: https://exceleratorbi.com.au/self-referencing-tables-power-query/

This tactic requires your data to have a unique identifier though. My data originally didn't, so I improvised and just did a drag and fill of sequential numbers. Once I did that, it worked as advertised.

 

[peter789]

Glad it worked. By the way it is better to use the add index feature in PQ rather than manually create and maintain the column of numbers back in Excel.

Peter

 

[ralphy32]

Glad it worked. By the way it is better to use the add index feature in PQ rather than manually create and maintain the column of numbers back in Excel.

Peter

I certainly like this idea, but it doesn't work in my case. The manually entered data disappears after refresh and the query generates an error. I have tried many different ways to add a unique identifier to my data, and anything that involves a formula seems to "break" the query or cause it to behave in an unintended fashion. Such as not retaining manually entered data after a refresh.

I greatly appreciate your help. You've gotten me much further than I would have gotten on my own. My data is 95% automated, and I'm ok with that.

 

[peter789]

I'm surprised it doesn't work: speaking from experience with using this technique on a number of applications.
You need to add the index on the first query after you merge or combine the data from the 3 Excel tables. When you have added the manual data send the results back to PQ. Then re-merge the results from the new query, after only keeping the index and the manual fields, with the join on the index. You have probably worked out you get duplicate columns but after deleting the unwanted ones they should stay gone. However if you are happy with what you have the old adage "if it ain't broke don't fix it" applies!
Peter

 

[ralphy32]

I was adding the Index too soon. By removing it and putting where you suggested it worked. Now it feels like this is truly automated. I can't thank you enough.