Power Query and protected sheet in Excel

We made a report. We'll give that report to some colleague. He will refresh that report periodically and he will then send it to end users. In the image bellow we can see that our colleague has to prepare 4 Access files as data source, and to open our report (1). He will than refresh it (2). At the end of refreshing MsgBox (3) will appear. After this, our colleague can save the file and he can then send it to other colleagues (4). Idea is to make this process as much easier as possible for our colleague.

One more requirement is that spreadsheet with tables has to be protected so that users can not accidentally change or delete some numbers. When we try to make report, as described, we will find few problems to do so:
1) Tables on protected sheet will not be able to refresh.
2) Combining Power Query and VBA asks for queries to be synchronous, which will prolong queries execution.
3) If we can not use VBA, how to create MsgBox (3)?
Let's see how to solve this problems.

Tables on Protected Sheet Will Not Refresh

When we try to "Refresh All" tables that are located in protected sheet, this is the message we will get. We will have to remove our protection before refreshing our data, and then to protect the sheet again after. Idea is to avoid such step and to automate things.

We could try to solve this problem by using VBA. Usually we would lift the protection, do the changes, and then set protection back. We can use VBA code similar to this bellow:

Sub RefreshTables()
ThisWorkbook.Worksheets("Queries").Unprotect Password:="fff"
ThisWorkbook.RefreshAll
ThisWorkbook.Worksheets("Queries").Protect Password:="fff"
MsgBox "All Tables Refreshed"
End Sub

Problem is that Power Query queries are executed asynchronously. Queries will still be executing (2), VBA will not wait for them to finish, but will show final MsgBox immediately (1). This means that protection on the sheet is already placed. After we click on OK button (1), we will again receive message (3) because of that.

As explained in this post, all we need to do is to make queries synchronous. This is done by unchecking option "Enable background refresh" in query properties.

Unfortunately this would make Refreshing lasting much longer. Instead of executing all of the queries at the same time (1), queries would be executed sequentially, one by one (2). This makes refreshing lasting two times longer, on my computer.

We can try to protect our cells by making some data validation rule that will never be fulfilled. This would prevent users from overwriting numbers in our cells, but it will not stop them from deleting values in our cells. Data validation is only triggered when cell is in Edit mode and the user is trying to enter some data into cell. Edit mode is not needed for deleting cell content, we just select the cell and press Delete key.

Solution for protected sheet

Best solution is to place refreshable tables in one sheet that is unprotected and hidden (2). Numbers from that sheet can be mirrored (3), by using formulas, in another sheet which is protected (4). Cells in protected sheet always have the same, unchanged formulas (5), so they will never trigger sheet protection.

User can now click on Refresh All button in Data tab (1). Tables in unprotected sheets would refresh, and tables in protected sheet would just reflect those results.

MsgBox without VBA

We said that synchronised queries would take longer to execute, so we will avoid VBA. Question is how to create MsgBox at the end of refreshing that would inform user that refreshing is over. The only way to create message in Power Query is by using "error" statement. We have to make sure that "error" statement happens at the end of refreshing when all individual queries are finished.

We can create a query like the one bellow. This query is referencing all other queries. This means that the query bellow has to be executed the last. At the end of refreshing, this query will show user the message that is written in Error.Record. This query has to be loaded into spreadsheet in order to work correctly.

let   
  Result = if { FactSales1, FactSales2, FactSales3, FactSales4 } = { "A","A","A","A" } 
    then "A" else error Error.Record( "Finished", "Refreshing is over." )
in
    Result

We can see bellow that the final query is dependent on all other queries (1). This MsgBox query will never load any data into spreadsheet, so its footprint will be like (2). This query will show us a message that looks like (3) after all other queries are refreshed.

Sample files can be downloaded from here. File "ContosoSales1.accdb" should be copied three more times to create files "ContosoSales2.accdb", "ContosoSales3.accdb", "ContosoSales4.accdb". In "Parameters" Excel sheet, change fullpaths toward those MS Access files.