SOLVED

Power query Refresh paused as file is open by another user on SharePoint Site

New Contributor

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? 

5 Replies

@Fuisdale2 

 

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?

Hi @Yea_So

This shared workbook is the workbook that has the power queried data in.

@Fuisdale2 

 

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?

The file it links to has sensitive Human Resources information. The Human Resources team only want to share key information from their master document. They didn't want to be copy and pasting information every time an update is required to be shared to people
best response confirmed by Fuisdale2 (New Contributor)
Solution

@Fuisdale3 

 

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.