Chapter 16 Automating Refreshing

As you build more and more solutions that leverage Power Query, and as you realize how much time it saves you, you’re bound to become hungry for more automation in your life. Yes, you can simply right-click a table that comes from Power Query, but even that will begin to feel so . . . manual. Wouldn’t it be better if you could just schedule an update, or maybe control the order in which things update?

Options for Automating Refreshing

You can actually use a few different methods of automating the refreshing of Power Query solutions:

Each works differently and has its own benefits and drawbacks, as you’ll see in this chapter.

Scheduling Refreshes Without Code

The first two methods for automating refreshing that we’ll explore are both set through the user interface and don’t require any VBA code whatsoever. They can be configured on a connection-by-connection basis, and you can even automate the refresh to Power Pivot if desired. Each of these connections is controlled by navigating to the Workbook Connections dialog in Excel (not Power Query):

This launches the following dialog, where you can check the box to update the query when the file is opened:

Figure 195 Setting connection options.

Background Refreshing

In the Connection Properties dialog, notice that the Enable Background Refresh checkbox is selected by default. This setting allows you to specify whether you’d like to keep working in Excel while the data refreshes. If you uncheck this box, you could potentially decrease the amount of time it takes to refresh your solution, but you’ll also lock out the user interface, which means you won’t be able to do other things until it is complete.

If you want to prevent users from working with the data until it is fully refreshed, you want to disable this setting. If you need to do other things while you wait, however, this setting is best left alone.

Refreshing Every x Minutes

The next available setting in the Connection Properties dialog lets you tell Excel to refresh the Power Query query every x minutes. When you check the box, you can set how often you’d like the data to be refreshed. This setting is fantastic if you’re pulling data from a web source that is constantly changing or if you are targeting a database that is being updated regularly, as it assures you that your data is always kept up-to-date while you’re in the file.

Keep in mind that the workbook needs to be open in order for this refresh to occur. And if you’re going to be scheduling frequent refreshes while you are actively working in the workbook, you’ll want to make sure the Enable Background Refresh setting is checked.

Note: Valid values for this setting run from 1 to 32,767, which means you can refresh once every minute up to once every 22.75 days.

Refreshing When the Workbook is Opened

This selection in the Connection Properties dialog actually allows you to specify two components:

The first one is rather self-explanatory, and checking the box for this will change the behavior of the workbook to do exactly what it says: refresh the data each time you open the file. This helps ensure that your data is always up-to-date when you start working with the file.

If you have a significant number of data sources, or if the data takes a significant amount of time to refresh, then it may be a good idea to leave the Enable Background Refresh setting enabled so that you can use the workbook while the refresh is occurring.

The second choice in this section controls whether to save the data in the workbook or only the query definition. This setting is actually a security setting, as it ensures that your users have access to the data source when they open the workbook. If they don’t, they’ll be greeted by a blank table, as the connection cannot be refreshed. If they do have access, the query will run and bring in the data.

Warning: There is currently a bug in all versions of Excel that affects the last option. If you open Excel and then open the workbook, the query will refresh, and the data will be loaded. If you then close the workbook and reopen it (without closing Excel), the data will not refresh automatically. Closing Excel fixes the problem.

Automating Query Refreshing with VBA

The options described so far in this chapter allow you to refresh Power Query queries with no macro security warnings at all. In addition, workbooks using the options described above are easier to port to Power BI, as they don’t cause any blocking issues.

If you’re working purely in a desktop Excel instance, however, there are times when you may want to give a user an easy-to-use and obvious way to update your Power Query solutions. This can be accomplished via recording VBA macros.

Refreshing a Single Connection

You can build a macro to refresh a single Power Query connection. To see how this works, open Ch16 Examples\Automating Refresh.xlsx and navigate to the Transactions worksheet.

On this worksheet you’ll find a Transactions table, as well as a PivotTable. Say that you’d like to create a macro to update the Transactions table and then the PivotTable.

To do this, you can record a simple macro by using the following steps:

Note: If you don’t see the Developer tab, right-click any tab on the ribbon and choose Customize Ribbon. Check the box on the right-hand side, next to Developer, and then click OK.

Figure 196 Start recording a macro.

Warning: Once you’ve clicked this button, Excel starts recording every worksheet click, every keystroke, and every mistake you make. Follow the steps below with precision to make sure you get a clean macro!

The macro is now recorded and ready to use. To test it:

You now see the Macro dialog, which allows you to see what macros are in your file and run any of them. Select the Refresh macro—it may be the only one you have—and click Run:

Figure 197 Running your macro.

When you run the macro, you can see that the Transactions table refreshes, followed by the PivotTable. (Of course, this would be more obvious if the data changed, but the data source is static.)

As great as this is, sending your users back to the Developer tab to run the macro on a regular basis is a little scary. Rather than do that, why not give them a button to refresh the macro? Follow these steps:

The Assign Macro dialog pops up with your macro in it.

You now have a nice, shiny, new button, all ready to use:

Figure 198 Launch button, ready to activate!

Go ahead and click the Refresh button and revel in the fact that any user can now refresh your query.

Note: If you ever need to edit the button, right-click it. When the little white bubbles surround it, it is in Design mode and can be modified. Select a cell in the worksheet to remove the selection handles and put it back into Active mode.

Refreshing Multiple Connections

The next concern that you might want to tackle is adding more queries to the macro and controlling the order in which they refresh. You can easily do these things by modifying the macro.

At this point you see code like this:

Sub Refresh()

‘

‘ Refresh Macro

‘

‘

ActiveWorkbook.Connections("Power Query - Transactions").Refresh

Range("G6").Select

ActiveSheet.PivotTables("PivotTable1").PivotCache.Refresh

End Sub

Here’s what this macro code does:

You can make some modifications to this macro to not only control the order in which all the connections refresh but also make the code a bit more bulletproof. (Right now it would fail if someone tried to run it from a different worksheet since it wouldn’t have the PivotTable on it.) Here’s what the code should look like after you revise it:

Sub Refresh()

ActiveWorkbook.Connections("Power Query - Jan2008").Refresh

ActiveWorkbook.Connections("Power Query - Feb2008").Refresh

ActiveWorkbook.Connections("Power Query - Mar2008").Refresh

ActiveWorkbook.Connections("Power Query - Transactions").Refresh

Worksheets("Transactions").PivotTables("PivotTable1"). _

PivotCache.Refresh

End Sub

Note: The space and underscore characters are the characters you use to indicate a line break in VBA code. The code will run equally well if the PivotCache.Refresh line remains on the same line as Worksheets(“Transactions”).PivotTables(“PivotTable1”). Just make sure that there is no space between the period at the end of the first line and PivotCache.Refresh.

You can see that you need to remove the unneeded code comments first. After that you simply inject new lines in order to refresh the specific connections in the order in which you want them refreshed.

In addition, by specifying Worksheets(“Transactions”) in place of ActiveSheet, you eliminate the need to select the PivotTable, and you also ensure that you are always refreshing the PivotTable on the Transactions worksheet.

Be aware that connections may or may not include the name Power Query. These names must match the names that are shown in the Workbook Connections dialog, as shown below:

Figure 199 Determining the names of your query connections.

Note: In midsummer 2015 Microsoft decided to rebrand (or debrand) Power Query due to concern that the term Power scared users away from the feature set. To this end, queries that you create today are not prefixed with “Power Query –” as they are in the dialog above.

When you click the Refresh button now, each query refresh is kicked off in turn, and the PivotTable is refreshed.

Warning: One thing to be aware of is that you cannot save your workbook in XLSX format once you have a macro inside it. Instead, you need to save the workbook in XLSMor XLSB format to preserve the macros. This way, users get a macro security warning message when they open the workbook before they can use your button to refresh the data.

Refreshing All Power Query Queries

In order to refresh all Power Query queries in a workbook, you need to use a slightly different block of code. The following macro will look through all connections in the workbook and identify whether they are created by Power Query (and it ignores all others):

Public Sub UpdatePowerQueriesOnly()

Dim lTest As Long, cn As WorkbookConnection

On Error Resume Next

For Each cn In ThisWorkbook.Connections

lTest = InStr(1, cn.OLEDBConnection.Connection, _

"Provider=Microsoft.Mashup.OleDb.1")

If Err.Number <> 0 Then

Err.Clear

Exit For

End If

If lTest > 0 Then cn.Refresh

Next cn

End Sub

This macro can be stored in the same workbook as the one created in the previous section, or it can replace the previous code (although you’d need to relink your button to the new macro instead).

Warning: Be aware that the code above will not necessarily refresh the queries in the order in which they need to be refreshed, as Excel refreshes queries in alphabetical order. Fortunately, you can change the name of the queries by going to Data → Connections → Properties and modifying the connection name. You can rename each query into an order such as 01-Jan2008, 02-Feb2008, 03-Mar2008, 99-Transactions. By doing this, you can ensure that a Power Query refresh runs in the correct order.

Automating Refreshing and more via Third-Party Add-ins

While VBA works well on desktop solutions, it is a total nonstarter if you want to port solutions to the web, whether to SharePoint, Office 365, or Power BI. For this reason, the team at power-planner.com built an add-in called Power Update to help solve these issues.

Here’s how the company describes the product in its own words:

Power Update is a brand-new software utility designed from the ground-up as a “companion” to Power Pivot, Power Query, and the entire Power BI stack.

It allows you to schedule ANY Power Pivot / Power BI workbook for refresh, regardless of data sources used, and automatically deploys/publishes the resulting workbook to a destination of your choice: SharePoint (both on-premises and cloud), OneDrive, Power BI online, file folders and network shares, and even Tabular SSAS servers. 

Even when paired with O365, Power BI, or other cloud server, Power Update does NOT require the configuration of gateways, VPN’s, or firewall ports. Furthermore, Power Update supports auto-refresh of Power Query workbooks as well as loading Power Query and PowerPivot data into SQL Server, finally allowing the industry to utilize PQ in “on-premises” production environments.

Finally, Power Update is NOT restricted to “once a day” refresh, nor is it subject to “rowset throttling” by Power BI version 2’s 10k rows/hr (free version) or 1MM rows/hr (Pro) limitations.

If this product sounds interesting to you, or if you’d like to read more about the features in Power Update, see the following blog posts by Rob Collie of PowerPivotPro.com:

http://mrx.cl/intropowerupdate, http://mrx.cl/freepowerupdate, http://mrx.cl/ssisalternative,

http://mrx.cl/buildingdatazen

The free version of the Power Update tool can be downloaded directly from:

http://www.power-planner.com/Products/ProdID/10