There is a quiet tragedy in the story you shared, and it is not the one about broken refresh buttons or legacy connection errors. The real issue is that you did everything right, you built a clean, thoughtful Power Query solution that combined twelve monthly tables per workbook, appended them, and created a pivot table that gave you exactly the summary you needed. It worked. Then it stopped. Not because your logic failed, but because the environment you are working in, SharePoint, Excel Online, and the shifting permissions that come with them, does not reward careful construction. It rewards constant vigilance, and nobody has time for that.
What you are experiencing is the gap between what spreadsheets promise and what they deliver in a collaborative, cloud-first world. You are not asking for anything exotic. You have five workbooks with identical structures, a folder on SharePoint, and a need to see where you stand across all locations. That is the most reasonable request in the world. Yet the moment you try to make it refresh automatically, you run into a wall of error messages about legacy connections and organizational accounts. The tools you were told would make this simple are, in fact, the very thing making it fragile. You even tried the SharePoint Folder approach and found yourself manually combining January tables for five files, then facing the prospect of repeating that eleven more times. That is not a workflow. That is a punishment.
Here is what your experience tells us, and it is worth saying plainly: the refreshability of a query is only as good as the permission model it sits on top of. You did not break anything. The system did. When you open the Power Query editor, you are operating with a different set of credentials and a different session context than when Excel tries to refresh from the data model in the background. That is why your editor refresh works and your spreadsheet refresh fails. It is not a mystery to be solved with more careful steps. It is a structural flaw in how Excel Online handles authenticated data sources across multiple workbooks. The sooner you stop blaming yourself, the sooner you can make a pragmatic choice.
And that choice is not to keep fighting the folder. You need to consolidate the data at the source, not at the query level. If you have the ability to maintain a single workbook, even a hidden one, that stores all the tables from all five locations in one place, you remove the dependency on SharePoint folder permissions entirely. Yes, it means a bit of manual upkeep or a scheduled script to pull data together. But a refresh that works on a single file, with a single connection, is a refresh you can trust. Your current approach is clever, but cleverness does not survive contact with enterprise IT. Simplify the data flow, and you simplify your life. You deserve a solution that does not require a ritual dance of re-authentication every morning. Build for durability, not elegance, and the pivot table will take care of itself.