Forum Discussion

Syndicate_Admin's avatar
Syndicate_Admin
Administrator
3 years ago

Power Query refresh removes manually entered data (excel)

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...
jst_jdennis's avatar
jst_jdennis
Frequent Visitor
3 years ago

  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*