Forum Discussion
Power Query locking when someone opens a separate referenced Pivot table file
I am a bit new to this but I have 5 queries built into the master power query file pulling from 3 folders with .csv files. The issue is not the .csv files refreshing, it is when the managers open the seperate pivot table xlsx file with 3 referenced pivot tables in it that I believe the auto refresh setting is for some reason sometimes actually opening the master query file on their comptuer and may not close it automatically once the refresh is done? The reason I do have the auto refresh setting on is becuase i didnt want the managers to always have to refresh the files every time they go in in case there is new data? just seemed simpler to do the auto refresh. i am going to see about pointing my files to sharepoint and see if that changes anything. initially when i made this a year or so ago it didnt seem like i could put this all up on O365 but now maybe that has changed.
I'd put it all on SharePoint/Teams in Office 365. 100% eliminates file locks. The same file can be open by 10 people simultaneously with no issues. The only thing that can cause a problem is if someone does "REFRESH ALL" it can kick the others out, but they can get right back in. Something about massive query refreshes confuses SharePoint. I would not put the queries in seperate workbooks to work around this unless that is a better workflow overall. If it makes sense for 1 workbook to have 10 queries in it, then do it in 1 workbook, not 10.
so I re-worked it to work on Sharepoint by making a new file and copying pieces of the queries to rebuild my tables. I did and still have 5 queries built into one Master file. then I have a separate file that just has pivot tables for the managers to see and manipulate as they see fit. but it seems like i cant refresh it using the web becuase it gives data errors but opening in the desktop app works. unfortunately it seems that the same happens with the pivot tables and unless I have the master file and pivot table file open in the desktop app it does not want to refresh for me? Not sure if I am missing something or creating someting wrong?