Final Friday Fix: August Challenge

Welcome to this month’s Final Friday Fix!  The inspiration for this month’s challenge comes from an actual problem that we’ve been trying to solve for some client work, that we couldn’t find an answer to anywhere online.  But first, let’s talk about refreshing queries – that is, Power Query queries in particular.  This Friday is going to be a little bit VBA, and a little bit challenge.

In VBA, you can trigger a refresh of your data connections, including your Power Query queries, using the following code:

Seems pretty basic, right? This is basically what happens when you click “Refresh All” in the Data tab in Excel 2016.

Now, in the bottom left hand side of Excel, when queries are refreshing, you would see a new message come up. This lets you know that queries are refreshing, and allows you to perform other actions in Excel while you’re waiting for it to work.

This is actually quite a vast improvement from the old days – you’ll see many blogs online complaining that you can accidentally hit ‘Esc’ and cancel the query refresh – this isn’t the case anymore! If you want to cancel your refresh, you can click on the message accordingly.

There are two main issues with this. The first is that Power Query (or Get and Transform) doesn’t tell you when your queries have finished refreshing. Ever watched a pot of water come to boil? The more you stare at it, the longer it feels like it takes! It’d be nice to have a message box pop up to let you know that your queries are done.

Of course, this leads us to problem two – Power Query doesn’t communicate back to VBA. So if I were to update the previous query as follows:

… this will trigger the message box to appear after the queries start to refresh, not once they’re finished. This can be solved by changing the queries to disable background refresh, but that’s not always a practical solution, especially when you’re working with large amounts of data, because Excel will be unusable while your VBA script is still running.

This brings us to our finale – the challenge question for this month’s Final Friday Fix. The challenge this month: can you create a file that will refresh your Power Query queries, and pop up a message box letting us know when it’s finished, without disabling background refresh?

Let us know if you’ve got a solution to this, and how you’ve done it. We’ll share on Monday what we think is the best way of making this work. Have a good weekend all, and happy querying!