Power Query locking when someone opens a separate referenced Pivot table file

[tmoore]

I created a power query to import .csv files from a few folders and reformat them / merge data from other files etc.  I created a sepearate Pivot table excel file that about 10 managers use to see the data in a nice clean way however sometimes when someone opens the pivot table file it physically opens the master power query file and it stays open and causes it to be locked so we can not refresh the power query data until they close it which some do not realize they have it open and it is not easy to figure out who has it open.  I did set the pivot table file to auto refresh on open so i dont know if that is causing the problem?  luckily we dont refresh the data too often but it has held us up for a few hours a few times so far.  

thanks

Todd

[tmoore]

So I remade the files in sharepoint and copied a big chunk of the query code and that part is working now.  But now I cant figure out how to have a seperate excel file that has pivot tables which point back to the original sharepoint excel file.  I created them using the desktop version of excel.  I made them in the master query file and then hit move or copy and moved them to a new sharepoint excel file but it seems like it is trying to reference a temporary folder on my C drive instead of the actual sharepoint xlsx file. when i try to refresh it.  Any thoughts?  Thanks again 

[edhans]

You'd have to show your M code. Your Source line in the original queries may be pointing to the C drive still, or you may even have a normal Excel formula reference. You are on the right path though. Nothing needs to be in the Excel file you want except:

• the Power Query/M code to extract the data from the Excel files on SharePoint.
• That data loaded to Excel's data model, along with any measures you need.
• The pivot tables themselves that reference the data model.

But not a bit of original data needs to be in that Excel file.

How to get good help fast. Help us help you.

How To Ask A Technical Question If you Really Want An Answer

How to Get Your Question Answered Quickly - Give us a good and concise explanation
How to provide sample data in the Power BI Forum - Provide data in a table format per the link, or share an Excel/CSV file via OneDrive, Dropbox, etc.. Provide expected output using a screenshot of Excel or other image. Do not provide a screenshot of the source data. I cannot paste an image into Power BI tables.

[tmoore]

I made a little bit of progress but it is still not working completely.  I dont see any formulas or anything in the M code or referencing local files that I can think of that would make it not work with sharepoint.  but i did change the way I created the pivot table in my seperate analysis file.  I went to "Insert Pivot Table" From external data source to choose my master sharepoint excel file.   It does work but the data will only refresh now if I have the desktop version of both files open but if I close the master file the pivot table in the analysis file wont refresh anymore and gives me this error "we couldnt get the data from Revenue_Master[#Data] in the workbook "mywebpath"/P & L Master File.xlsx' Open this workbook in Excel and try again.  when I try to refresh connections through the Edge browser I get an error message that says "we cant open the source file "webpath"/P & L Master File.xlsx' we are unable to refresh one or more data connections in this workbook.   The following connections failed to refesh:  WorksheetConnection_P & L Master File.xlsx!Revenue_Client_Master.  I guess i could try making a new Power Query master file and building a piece of the code from scratch through sharepoint to see if the data connection works that way to determine if it is my M code or soemthing else.  I can certainly share my m code if needed.   Would I be better off just building the pivot tables right in the master data file instead of having seperate files?  not sure whats best.  but I thought keeping the large data tabs out of the file the managers go into would make it faster, safer and cleaner but if sharepoint / online excel cant handle that I can certainly change course?

[tmoore]

so I made a new master file with new simpler queries from scratch and it works the same way as my previous file so I think my power query is ok.  it will refresh if I have the desktop excel app open but not via the web still though.  Is that a limitation of this type of thing?  Next I created a pivot table in another file and when I try to connect it to my sharepoint master file it seems like it works but it seems like it is using  a  local folder path to my master file instead of using actual sharepoint web path for the refresh.  I cant figure out how to make it connect to the actual file on sharepoint.   So it seems like it might work ok for me but if someone else tries to refresh the path will try to use my local onedrive path with my username in it. C:\Users\tmoore\Budget - Documents\P & L Analysis\P & L Experiment 1-26-22.xlsx .  Google isnt much help either.  Am I just trying to do more than this process can support?  or am I just making the connection in the wrong way or missing something?    

[watkinnc]

Can't you just copy and paste the queries into the different workbooks, so everyone can use the workbook? I can't imagine having to do it the way you described...what's it a credentials issue?

--Nate

[tmoore]

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.    

[edhans]

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. 

[tmoore]

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?

[edhans]

Either set the file to not refresh on open, or move the CSV files to SharePoint libraries. Those do not cause a lock and multiple apps can read them at the same time. This is one of many reasons I store files in SharePoint vs local network or local PC drives. I never deal with file locks for data source files.

[tmoore]

Thanks.  I will give that a try.  I am a bit new to Power Query.  this is my first project using it as opposed to writing lengthy macros.  would my master power query file go on sharepoint as well?  or just the .csv files i am ingesting?