This reader's problem is exactly the kind of friction that makes people resent their own tools. When a helper query, created automatically by Excel during a folder merge, refuses to update its source alongside the main query, the software has failed to deliver on its promise of automation. The user did everything right: changed the data source via the ribbon, refreshed all, and expected consistency. Instead, they got a broken query and a manual detour into the advanced editor. That is not a user error; it is a design gap.
What this means in practical terms is that anyone working with merged files from a folder is one dataset change away from a tedious repair job. The main query updates because Excel treats it as the primary connection. The helper query, though generated by the same process, is treated as a static artifact. The user must either remember to update it manually every time, or dig into the M code to hardcode a new path. Neither option scales. If you manage multiple workbooks with frequent data refreshes, this single oversight can cost hours over a quarter.
The deeper issue here is about trust. When a tool creates something automatically, the user should be able to assume it will maintain that creation consistently. Excel's folder merge feature is powerful, but it introduces a hidden dependency that most users will not discover until something breaks. The solution exists, parameterize the source path, or write a script to cascade the change, but that requires a level of expertise the average spreadsheet user should not need just to keep a refresh running. The tool should handle its own housekeeping.
Our opinion is straightforward: if a query is created by the system, the system should own its lifecycle. A helper query is not a convenience if it becomes a liability. For now, the workaround is to treat that helper query as part of your refresh checklist, or to restructure the workflow so the helper query references a parameter that the main query also uses. It is not elegant, but it works. What would be better is for Excel to recognize that these paired queries share a source and to update them as a unit. Until that happens, the smartest move is to build your workflow around the tool's limitations, not against them.