I would like to get help with answering questions on how to share output from excel power to Sharepoint then to user\’s excel workbook?
Background:
Information to share is sales data, currently, i have 1.5 millions rows in total.
This information is saved in Microsoft Access (Source 1- update monthly), MTD sales info in Power query as a few data manipulation required on system data download ( Source 2-update fortnightly)and some additional information is in Excel tracking workbooks (Source 3 – update fortnightly).
This information is saved in SharePoint (Sharepoint A)where only management and Myself have access to.
Outcome require:
- Need to manipulate the sales data information from above 3 sources in power query as we do not want intended users to have full access into all information.
- Above manipulated data will share through another Sharepoint site (Sharepoint B) where all intended users has given access to.
- Users require to load these information to Excel workbook and use power pivot to analyse data.
My plan of resolution:
Step 1-Load information from combined Microsoft Access (Source 1) and Power Query output (Source 2) in one power query and create one query.
Step 2-And load Excel workbook (Source 3) into Power Query as another query.
Step 3-Share this to queries in Sharepoint B where the intended user has access to
Step 4-create individual Excel workbook for 12 users file and Load 2 queries in the workbook (use excel feature such as Power Pivot/Pivot table to analyses)
Knowledge Gap:
- I have no idea how to export queries from the excel workbook/ power query, then save in Sharepoint and how to refresh or update these queries.
- Once it is in SharePoint, I am not sure how to load into excel workbook (not sure would it be as easy as using Get Data from Sharepoint function in excel)
It would be great if anyone can guide me in getting the answer for the 2 points above as they are my main concern. I would also be grateful if anyone can correct me on all my approach and setup of these files.
It seems that you want to share the query in SharePoint and then update them automatically.
If my understanding is correct, here are my test result:
1. Open an Excel table, and create queries from the Hosted Access database or another workbook.
2. Upload the original table and the query table to SharePoint.
3. Open the query in local and select Data>Refresh all>Connection Properties, then check the option” Refresh data when opening a file.
Image
4. Finally, change in the query will update automatically in SharePoint each time you open the file.
If you want to load two 2 queries in an individual Excel workbook in SharePoint B, just create those two queries using get data feature in local Excel client and upload them in your library.
