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.RefreshFor refreshing a connection we can use this line of code:
ThisWorkbook.Connections("WorksheetConnection_New Microsoft Excel Worksheet.xlsx!SampleTable").RefreshIn 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: