Today, we’re diving deep into a feature within Power Query that can often lead to confusion: the Include in Refresh option. This feature plays a crucial role in how data is refreshed in Power BI, especially when working with multiple data sources. Let’s break down what it means, how it works, and the implications of using it.
In this blog, I’ll share examples and demonstrate how this feature impacts the data refresh process in Power BI. If you’ve ever faced issues where your data didn’t seem to update as expected, understanding this feature will be essential.
What is the Include in Refresh Option?
The Include in Refresh option determines whether a specific query or table is included during a full refresh of your Power BI model. When this option is disabled, the data from that table will not change during a refresh, even if the underlying data source has been updated. This can lead to situations where your reports display outdated information.
To illustrate this, let’s take a look at a scenario involving two Power BI Desktop files. The first file connects to an Azure SQL database, while the second pulls data from a local Excel file. The key difference is how the Include in Refresh option is set for each data source.
Example Scenario 1: Azure SQL Database
In our first example, we have a Power BI file that connects to an Azure SQL database. Inside Power Query, you might see that the Include in Refresh option is not selected for a table pulling data from an Excel file. This means that when you perform a full refresh, this table will not be updated, and any changes in the Excel file will not be reflected in your Power BI report.
For instance, if the Excel file originally contained the year 2013 and you update it to 2019, after performing a full refresh, the Power BI report will still show 2013. This is because the Include in Refresh option was disabled, keeping the table static.
Example Scenario 2: Using as a Filter
In the second example, we have another Power BI file where the Include in Refresh option is also disabled for a filter table. However, this time, the table is being used as a filter for an Internet Sales table. Even though the Include in Refresh is disabled, the query still runs because it is referenced in the context of the Internet Sales table.
This means that even though the filter table isn’t refreshed as part of the overall refresh, the Internet Sales table will still process the filter value from the filter table when it’s being executed. So, if the filter table still holds the value 2013, the Internet Sales table will pull data based on that, resulting in a discrepancy if the filter table was supposed to reflect a more recent year.
How to Refresh Individual Tables
Let’s say you want to update the filter table despite the Include in Refresh option being disabled. There are a couple of methods to refresh individual tables manually. One method is to right-click on the table in Power BI Desktop and select Refresh Data. This will refresh that specific table without needing to refresh the entire model.
Another option is to use the XMLA Endpoint if you have Power BI Premium. This allows you to selectively refresh individual tables using tools like SQL Server Management Studio.
Understanding Gateway Settings
Another important aspect to consider is how the Include in Refresh setting interacts with your gateway settings. Even if you disable the Include in Refresh option, you may still need to configure a gateway for the data source. This is because the connection to the data source still exists within the semantic model, and credentials are required for any queries that reference that data source.
In essence, unselecting Include in Refresh does not eliminate the need for a gateway. You still need to provide credentials, as the connection remains active in the background, even if the table itself is not being refreshed.
Common Pitfalls
It’s crucial to be aware of common pitfalls when using the Include in Refresh option:
- Static Data: If you disable the Include in Refresh option, be prepared for that data to remain static. This can be problematic if you expect the report to update based on changes in the source data.
- Unexpected Refresh Behavior: Just because a table isn’t included in the refresh doesn’t mean it won’t impact other queries. If it’s referenced elsewhere, it can still affect the outcome of those queries.
- Gateway Confusion: Users often assume that disabling Include in Refresh means they can skip gateway configurations. This is not the case, as the connection still requires authentication.
Best Practices for Using Include in Refresh
To effectively manage the Include in Refresh feature, consider these best practices:
- Evaluate Data Needs: Before disabling the Include in Refresh option, assess whether that data is truly needed for the report or if it can remain static without impacting analysis.
- Document Your Choices: Keep track of which tables have the Include in Refresh option disabled to avoid confusion during refresh cycles.
- Test Refresh Behavior: After making changes to the Include in Refresh settings, always test to ensure the expected data is being displayed in reports.
Conclusion
Understanding the Include in Refresh option in Power Query is essential for effectively managing data refresh processes in Power BI. By being aware of how this setting impacts your data and utilizing best practices, you can avoid common pitfalls and ensure your reports reflect the most current information.
For those looking to deepen their knowledge about Power BI and data management, consider exploring more resources and training opportunities, such as the expert-led Power BI training available.
Let me know in the comments if you have further questions about this feature or any other aspects of Power BI. I’m here to help!
