Manually Entered Data Shifting After Refresh

ralphy32

New Member
Joined
May 8, 2019
Messages
4
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
 

Excel Facts

Can you sort left to right?
To sort left-to-right, use the Sort dialog box. Click Options. Choose "Sort left to right"
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
 
Upvote 0
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.
 
Upvote 0
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
 
Upvote 0
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.
 
Upvote 0
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
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,213,506
Messages
6,114,024
Members
448,543
Latest member
MartinLarkin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top