How to Refresh Only Subset of Tables

Often, we do not want to refresh all the tables in Power Pivot or Power BI Desktop. We can choose subset of tables to refresh, both in Power Pivot and Power BI Desktop, as a permanent settings. It is also possible to select tables to refresh with mouse and only those tables will be refreshed. For Power Pivot we can use VBA to select tables to be refreshed.

Power BI Desktop

In Power BI Desktop, all you have to do is to uncheck the "Include in report refresh" option, for some of the tables, and those tables will not be part of refresh. This option is located in the contextual menu for each query, in Power Query window (1). We can see that only tables SampleTable1 and SampleTable2 will be refreshed (2) if we click on Refresh button in Ribbon. This is because we unchecked this option for SampleTable3 and SampleTable4.

Beside "Include in report refresh" option, which is permanent, we can choose which tables to refresh by selecting them in model view. Holding Ctrl key and clicking on the table headers in model view, we will select subset of tables. On any of those tables we can open contextual menu with right click. Refresh button is in this menu. Now only, tables SampleTable2 and SampleTable4 will be refreshed, because only those tables are selected.

Power Pivot and Power Query

Similar to Power BI Desktop, Power Pivot also has option to permanently disable refreshing of some tables (1). This option is placed in Properties for Queries and Connections (2). Queries are made by Power Query and for them we can control Refresh status for each query.

If the data is retreived using Power Pivot directly, without Power Query, we can only set refresh setting for each connection. This setting is valid for all tables that are loaded by using that connection. If we want to control refreshing of each table separatelly, we have to create separate connection for each of the tables. On the image bellow we can see that we have two connections made to the same Excel file. This is solution for having full control.

In Excel, we can also select tables in Diagram view (1). After this we should click on "Refresh" (not Refresh All), and only selected tables will be refreshed (3).

VBA

If name of query in Power Query is "SampleTable" then by using this line of code we can refresh only that query with VBA.

ThisWorkbook.Connections("Query - SampleTable").OLEDBConnection.Refresh

For refreshing a connection we can use this line of code:

ThisWorkbook.Connections("WorksheetConnection_New Microsoft Excel Worksheet.xlsx!SampleTable").Refresh

In this case it is important whether this query will be executed asynchronously. This article explains how to change that setting.

Excel and Power BI Desktop sample file:

Volatile, Reference, Case Sensitive Functions

Volatile functions

Volatile functions are recalculated each time any cell in the spreadsheet is changed. Some other actions can also cause recalculation, such as renaming sheets, inserting columns, deleting rows, etc. Any formula that contains a volatile function will also become volatile. Any cell that depends on a cell that contains volatile function will also become volatile. Too many cells that contain a volatile function in a spreadsheet will significantly slow down that spreadsheet.

Volatile functions are RAND, RANDBETWEEN, RANDARRAY, NOW, TODAY, OFFSET, INDIRECT. INFO and CELL functions can be volatile depending on which arguments they use.

UDF functions can become volatile if the statement "Application.Volatile" is placed in front of them. The function below is nonvolatile.

Function UnVolatileFunction()
UnVolatileFunction = ActiveCell.Offset(-2, 0).Value + ActiveCell.Offset(-1, 0).Value
End Function

In contrast, this function is volatile because it contains an "Application.Volatile" statement..

Function VolatileFunction()
Application.Volatile
VolatileFunction = ActiveCell.Offset(-2, 0).Value + ActiveCell.Offset(-1, 0).Value
End Function

Excel Functions That Return References

Excel functions that return references are OFFSET, INDEX, XLOOKUP, CHOOSE, SWITCH, IF, IFS, INDIRECT. This means that the result of the function is neither a scalar nor an array. The result is the address of the range in the table.

Although the reference refers to multiple cells, Excel 365 or 2021 can directly display the result of these functions as a spill (1). In older versions of Excel, it was necessary to convert these functions into array functions with Ctrl + Shift + Enter in order to display their result directly (2). If a given range contains only one cell, all versions of Excel will return the contents of that cell (3). If a range contains more than one cell, it has always been possible to wrap that range in an aggregate function that will result in a scalar that can be displayed in a spreadsheet (4).

Case Sensitive Excel Functions

In Excel, there are three functions that are case sensitive. Those are EXACT, FIND and SUBSTITUTE. There are also XLOOKUP and XMATCH functions that have arguments that can be used to control whether the functions are case sensitive. Those functions are available in Excel 365 and 2021.

Direct comparison of A1 and B1 cells will return TRUE (1), but if we use EXACT function, result will be FALSE (2).

How to unpivot Excel tables

Unpivot by using pivot table

This technique is only useful for unpivoting tables with smaller number of columns. Power Query is preferable for larger tables.

We have a table with a pivoted column of office products. We want to unpivot that column.

All we have to do is to create a normal Pivot table. In this pivot table all unchanged columns should be placed into Rows section (1). All other columns, that should be unpivoted, should be placed in Values section (2). Notice that in Columns section we have a special column "∑ Values" (3), that Excel created itself.

Now drag that special "∑ Values" column into Rows section (4). We will immediately get unpivoted table (5). This table asks for some maintenance so is best to make a copy of it in spreadsheet and then fix its header, and filter rows with no values.

This approach could will not be optimal when there are many columns that should be unpivoted. In that case we have to move all those columns into Values section manually. This could be tedious. In that case it is much easier to use Power Query unpivot tool.

Unpivot by using Power Query

Get your unpivoted table into Power Query. Select all columns that should be unpivoted (1). In Transform tab (2) find command "Unpivot Columns" (3). This command will do all the work. Notice that we also have command "Unpivot Other Columns" (3). This allow us to select only columns that shouldn't be unpivoted, and then all the other columns will be unpivoted. When we have really big number of columns to unpivot, this command make it even more easier to accomplish our task.

At the end we get unpivoted table. Again, we have to fix column names (1). Rows without vales will be automatically filtered (2).

Excel sample file:

Instructional video:

PQ execute queries asynchronously

Power Query is great tool for preparing data to be written into CSV file. CSV files are usually used to import data into databases but the requirement is that they must be formatted in specific way. We can export formatted data to a spreadsheet, and then we can use VBA to write already formatted data to a CSV file.

Data connections in Power Query are by default set to be asynchronous. If we start a query with "Workbook.RefreshAll" from VBA procedure, the rest of VBA procedure won't wait for the query to complete. Instead of writing new data from the spreadsheet to a CSV file, VBA procedure will write the old ones, because there will be no time for the query to complete.

The solution is to change the execution of the query to synchronous. This is done in Excel Connection Properties. We can find our connection properties in Data tab (1) > Workbook Connections Properties (2). There is a checkbox "Enable background refresh". This checkbox should be disabled.

The checkbox can be disabled directly from the VBA. That possibility allow us to fully control the execution of a query in procedure. This procedure will temporarily change BackgroundQuery properties to False so that our Procedure will wait for the query to complete.

Sub Refresh_All_Data_Connections()
    For Each objConnection In ThisWorkbook.Connections
        'Get current background-refresh value
        bBackground = objConnection.OLEDBConnection.BackgroundQuery
        'Temporarily disable background-refresh
        objConnection.OLEDBConnection.BackgroundQuery = False
        'Refresh this connection
        objConnection.Refresh
        'Set background-refresh value back to original value
        objConnection.OLEDBConnection.BackgroundQuery = bBackground
    Next
End Sub

Excel sample table:

Connection to The Desired PBID file

Connecting to a Power BI desktop is easy if we have credentials available. It's not hard to read credentials. If more than one Power BI desktop file is open, the question is how to connect to the desired file.

Each time a PBIX file is opened, new credentials are written to the default location. That default location is usually:
C:\Users\<Username>\AppData\Local\Microsoft\Power BI Desktop\AnalysisServicesWorkspaces
If we have more files open, more credentials will be written. In the image below we see that three PBIX files are open.

Each of these folders contains a Data subfolder. Within that subfolder are two files that contain the address and name of the database we want to link to. The database name is in the XML file name (looks like "c6fb254f-f146-464d-99cf-b2a6e322eb38.1.db.xml"), and the database address is the number found in the msmdsrv.port.txt file (looks like "58139"). After closing the file, its credentials folder will be deleted.

Each time a PBIX file is opened, its database will have different credentials. It is not easy to make a direct link between the file name and its credentials. The problem is that we don't know which of the credentials to use to link to the desired file. We can solve this problem by creating a table that contains the PBID file name in each PBID file. If we know all the credentials, we can read file names from all tables. When we find the name we want, we will know which credentials to use.

We can now use Power Query to get the necessary data. We will first read the credentials for all three PBIX files.

Next, we will read the names of the open PBIX files and filter only the desired file. That will tell us which credentials to use.

Here are three PBID sample files and one Excel sample file: