Enable Load in Power Query (Power BI)
What is "Enable Load"?
- The Enable Load option in Power Query determines whether a query's results are loaded into the Power BI data model (or report) for further use.
- Found in the query settings under the "Properties" pane or by right-clicking a query in the query list.
Key Points
- When Enabled:
- The query's output is loaded into the Data Model in Power BI.
- Data becomes available for visualizations, measures, and other calculations.
- Part of the report refresh process when refreshing data.
- When Disabled:
- The query is not loaded into the Data Model.
- Still available for use in other queries (e.g., as an intermediate step).
- Not included in the report refresh or final data.
Why Use Enable Load?
- Optimize Performance:
- Prevent unnecessary queries from consuming memory and resources in the Data Model.
- Only load the final queries that are required for reporting.
- Intermediate Queries:
- For workflows that involve Extract → Transform → Load (ETL):
- Intermediate steps (e.g., raw data extracts, transformations) can have Enable Load turned off.
- Final queries that summarize or clean data are enabled for load.
- Reduce Clutter:
- Avoid unnecessary tables in the Power BI Fields pane.
What is "Include in Report Refresh"?
- Another option available for queries in Power Query.
- Determines whether the query will refresh its data source when the report is refreshed.
- Useful for scenarios where:
- Some queries are static (e.g., reference data or rarely updated tables).
- You want to save time by excluding such queries from the refresh process.
Key Differences: Enable Load vs. Include in Report Refresh
Feature | Enable Load | Include in Report Refresh |
Purpose | Controls whether the query loads into the model. | Controls whether the query refreshes its data. |
Impact on Performance | Saves memory by skipping unused queries. | Saves time by skipping unnecessary data refresh. |
Common Use Cases | Intermediate queries or hidden data. | Static or rarely updated queries. |
Best Practices
- Enable Load:
- On: For final queries required for reports or visualizations.
- Off: For intermediate queries used only for transformations.
- Include in Report Refresh:
- On: For queries that rely on frequently updated data.
- Off: For static reference tables or test data.
Example Workflow
- Extract:
- Query raw data (e.g.,
Sales_Data_Raw) with Enable Load OFF and Include in Report Refresh ON. - Transform:
- Perform transformations (e.g., clean data, join tables) with intermediate queries.
- Keep Enable Load OFF and Include in Report Refresh ON.
- Load:
- Final query (e.g.,
Sales_Data_Final) for reporting. - Enable Load ON and Include in Report Refresh ON to include it in the report and refresh process.
Practical Tip:
Regularly review your queries to ensure only the required ones have Enable Load ON to keep your Data Model optimized and refresh times manageable.
4o