Power Query refresh removes manually entered data (excel)

[Syndicate_Admin]

Hi 

So i have 750,000+ address records in a data source (Address_list.xls). These addresses are allocated to Teams (40 teams). I used Power Query to separate the Address_list.xls in to 40 Address Lists e.g. Team_1_Address_List

These are then loaded in to a workbook per Team. 

Now each Team needs to record against their allocated addresses, the time and date, and the outcome of their actions. 

1 - My first issue is that when I refresh the queries for each team, and new addresses appear in their lists, it doesn't keep their data inputs aligned with the correct records. How do i keep this from happening? or is it not possible?

2. Secondly I would need to collate (using the merge power query function) all the completed records that have an outcome recorded against them from all the teams lists and merge them in to one list.

Is it possible to query a query and then hardcode the result so it can't be changed again once an output has been inputed?

Any adivce on this solution would be amazing, I would prefer to have this in one spreadsheet for all 40 teams to access but i think that would be utter chaos?

Thanks in advance

[jst_jdennis]

1. When you refresh the queries for each team and new addresses appear, Power Query is designed to update the entire dataset, so any manually entered data may get overwritten. One possible workaround is to create a separate table within the workbook for each team where they can input the time and date, and the outcome of their actions. Then you can use VLOOKUP or INDEX-MATCH formulas to pull in the data from the separate tables into the team-specific address lists. This way, the manually entered data will not be lost during a refresh.

2. To collate the completed records with outcomes from all the teams' lists, you can create a separate query that merges all the teams' address lists together and then filters out any rows without an outcome recorded. You can then load this merged and filtered table into a separate sheet in the workbook where everyone can access it. To hardcode the result so it can't be changed again once an output has been inputted, you can copy and paste the values from the merged and filtered table into a separate table or sheet within the same workbook. This way, the resulting data will not be linked to the original query or the input data sources.

Credit: ChatGPT | https://chat.openai.com/chat

*This answer was generated by the AI chatbot ChatGPT*