Oct 12 2021 03:24 AM
Hi all,
I have a Power Query workbook which is shared and can be accessible by multiple people on a SharePoint site.
When someone makes an update and wants to refresh the Power Query workbook they get an error message to say another person has the workbook open and the query is paused.
Even going into the Data option and clicking refresh doesn't work..
Any ideas on a work around?
Oct 17 2021 12:45 AM
You stated:
I have a Power Query workbook which is shared and can be accessible by multiple people on a SharePoint site.
To clarify and confirm:
This shared workbook is the workbook that has the power queried data? or this shared workbook is the power query data source?
which is it?
Oct 17 2021 01:07 AM
Oct 17 2021 01:19 AM
what is its sharing context? or its purpose why it is shared? or its usage?
so people can cut and paste data from it? or use the workbook for cell references to other user's workbooks?
Oct 17 2021 01:36 AM
Oct 17 2021 02:01 AM - edited Oct 17 2021 02:08 AM
Solution
So the shared file's source file has sensitive key HR information, but is only a subset of the HR master file.
a work around can probably be tried.
1. The updates must be saved as a csv file into a specific folder
2. The shared power queried file must use query by folder so if someone tries to change the power query all they see is just the same subset information that was saved as a csv file in the specific folder
3 I haven't tried this but give it a try save the shared power queried file as a template so when someone opens it for whatever reason it does not really open the original template file but a copy of it so any number of users can open (a copy of the shared file and still update the query...this is an assumption because like I said I haven't tried it but in theory its worth a try)
I don't have share point so I can't test my theory.
This method should be Sarbanes Oxley compliant.
Oct 17 2021 02:01 AM - edited Oct 17 2021 02:08 AM
Solution
So the shared file's source file has sensitive key HR information, but is only a subset of the HR master file.
a work around can probably be tried.
1. The updates must be saved as a csv file into a specific folder
2. The shared power queried file must use query by folder so if someone tries to change the power query all they see is just the same subset information that was saved as a csv file in the specific folder
3 I haven't tried this but give it a try save the shared power queried file as a template so when someone opens it for whatever reason it does not really open the original template file but a copy of it so any number of users can open (a copy of the shared file and still update the query...this is an assumption because like I said I haven't tried it but in theory its worth a try)
I don't have share point so I can't test my theory.
This method should be Sarbanes Oxley compliant.