Forum Discussion
Power Query locking when someone opens a separate referenced Pivot table file
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
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.
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?
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?