Can a Power Query in an open Excel file act as 'another user' and make the source file 'read only'?

[SledgeNL]

PQ holds source file open

We have the following situation:

computer_1: file_A with Power Query to file_B, file_A is being worked in, but not necessarily the PQ.

computer_2: in a scheduled task it tries to update file_B, but then gives the messages that file_B is "read only". This message is handled by a person and the task ends incomplete.

Realising that file_A, that is still open on computer_1 that has a PQ to file_B, we close file_B and run the task on computer_2 again. This time the task ends succesfull.

Which leads to the suspicion that just having a PQ to file_B in open file_A , will render file_B to become 'read only'.  

How valid is this suspicion?

We also run sharepoint, so the syncing there could be a factor too. I suspect this is not for this forum. 

Original post:

Can a Power Query in an open Excel file act as 'another user' and make the source file open 'read only' for another user?

Is there a way to prevent this? I'm thinking about the options for refreshing a Power Query (in the background, upon opening, when refresh all is selected.. ).

We have a file with historic spot prices that is updated every day (by the task scheduler) with data coming from another file (copy-pasting with both files open).

It happens frequently that this history-file opens 'read only' "because another user has it open" - with the consequence that it can't save. 5 files that are scheduled directly behind this task then run into an error because the prices aren't updated.

We also have quite some files that have a Power Query to this history-file.

By accident we found out that when we closed a file we were working on that had a PQ to this history-file the scheduled task for the historic prices was able to run completely when triggered again in the task scheduler.

Can a Power Query in an open Excel file act as 'another user' and make the source file open 'read only' for another user?

Is there a way to prevent this? I'm thinking about the options for refreshing a Power Query (in the background, upon opening, when refresh all is selected.. ).

When I asked this question in the general Microsoft community I was told to ask the question here. 

[Anonymous]

Hi @SledgeNL ,
According to your description, when you use power query, the scheduled refresh can't be triggered at this point because the file is in read-only state at this point. This is because as a precautionary measure to ensure data consistency and prevent accidental modifications to the source data.
If you want to perform a refresh of other files while a power query is open, you can create a separate data file (e.g., CSV, Excel, or database) that is used as the data source for the Power Query instead of connecting directly to the source workbook. This way, the source data file will remain separate and can be accessed and modified independently without affecting the Power Query workbook.
Of course, if you need to get the latest data you can perform a power query refresh.
Refresh Power Query in Excel: 4 ways + advanced options - Excel Off The Grid

Best regards,
Albert He

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

[SledgeNL]

Thank you for your reaction. I realised that I didn't descibe the situation accurate enough. I will edit the original post to reflect the situation better.