I need a way to combine multiple workbooks to use in a pivot table that can be refreshed through sharepoint.

I have 5 spreadsheets in a folder on sharepoint and one on a separate folder in the same team site. These track contacts made by clients. Each workbook is for a LGA (location) and have the same structure. they have a sheet for each month and a table for that month.

I initially struggled to use the Sharepoint Folder to combine these so created queries in Power Query. for each workbook I created a query which combined the 12 tables and then created a query which appended them. I was able to put this in a workbook and a pivot table to summarise where we were up to. it worked really well. the permissions were set using organisational account.

this lasted about a day and now I cannot refresh it. I can refresh in the power query editor but not in the spreadsheet. I have opened it in the desktop version and also the same problem. I am now getting errors about legacy connections.

I then tried the Sharepoint folder which finally worked but seem I can combine the January tables for the five spreadsheets but then I would need to do the same for each month.

I would love any suggestions for this to consistently work and refresh from our sharepoint site (excel online).