-2

I have a workbook with 2 sheets containing the same list of clients, but with different data relating to the client. Both sheets use LastName and FirstName as the first two column headers. Both sheets have about 10 additional columns, each being unique.

Sheet 1 is the "master list" table with 90 current names. We insert a row and add new names as we get new clients. I am trying to get sheet 2, with 65 of the 90 names, to update dynamically when a new row and name is added to Sheet 1.

After researching, I found I could use Power Query to merge Sheet 1 with Sheet 2 and Load the Output to a new Sheet. Let's call it Merged.

It looks great, so I hid the original Sheet 2 because it is now outdated. I no longer want to interact with Sheet 2, I want to interact with the new Merged Sheet. Now when I add a row to Sheet 1, Merged updates nicely with a new row and Names in place.

Problem: I can't add or change data on the Merged Sheet (Query output) without it disappearing as soon as I Refresh. It reverts back to the original.

Can I overwrite/add data to the Power Query output and save those changes? Somehow preventing Sheet 2 from clearing out the new data. Is that even a thing?

Can I Only update one of the connections - Sheet 1 and leave Sheet 2 dormant?

Am I using Power Query when I should be using something like XLookup?

I want to get rid of the original Sheet 2. It has become obsolete now that 30 new clients have been added to Sheet 1, and will continue to fall behind.

Thanks for the help.

Archie Weber
  • 9
  • 3
  • It is unclear why you are keeping sheet2 around if you don't need it anymore. Merge in sheet1 and just use that output as your data then you no longer even need to use powerquery. That said if you must continue to use it and want changes to roll forward to next refresh the google self referencing tables like https://exceleratorbi.com.au/self-referencing-tables-power-query/ – horseyride Aug 10 '22 at 07:08
  • Thanks for the reply. I t prompted me to look around some more and I found the answer from Jenn at this link - https://stackoverflow.com/questions/59251979/when-a-row-is-added-to-one-sheet-automatically-add-a-row-to-another-sheet helpful. – Archie Weber Aug 10 '22 at 13:01

1 Answers1

0

Initially I was stumped on how to merge two sheets while shuffling the new names with the old, but a Power Query work for that problem.

Then I had the new table with the data in the correct position and order.

I found the quick tutorial from Jenn at this link; When a row is added to one sheet, automatically add a row to another sheet

This opened my eyes to the possibility that a Query can be only one connection. So I created a new sheet with the single query to the names on Sheet 1.

I copy/pasted the Merged info into the new sheet, and expanded the table range to include the new info and viola! I have a dynamically updating sheet based on the Sheet 1 list of names.

Now I can delete the original Sheet 2 and move forward and the original query.

Archie Weber
  • 9
  • 3