In this tutorial, you’ll learn how to reference tables in Power Query to improve refresh time and data performance in Power BI. You can watch the full video of this tutorial at the bottom of this blog.
Power Query has an option to set the refresh frequency for a query by selecting the “Refresh every” option in the Query Options menu. You can choose to refresh the query every minute, hour, or day, or you can choose to refresh the query manually.
Though sometimes, users can encounter errors when trying to refresh a query. One of the ways you can improve the refresh time is by referencing your tables.
Loading The Data Tables In Power Query
Open the Power BI desktop. Go to the Home tab and select New Source. If you recently opened the source file, you can click the Recent Sources option.
This opens the Navigator wizard. You can then select the tables of data you want to optimize.
Once the refresh process in Power Query is finished, go to each table you opened and rename them. This will make it easier to identify those tables as the original data sources.
If a table is taking too long to load, you can click the Refresh Preview option in the Home tab.
Improving Refresh Time In Power BI
Open one of the tables you want to optimize. Go to the Home tab and click Advanced Editor.
In the Advanced Editor window, focus on the first two lines of code.
Delete everything after “Source =” and write the name of the table.
Then, go to the next line and replace the table name with Source. Afterwards, click Done.
Repeat these steps on all the tables you want to optimize.
Note that different tables will have different lengths of codes in the Advanced Editor. But the format of the first two lines will remain the same.
Moving forward, you don’t want Power BI to load and refresh the same table twice. So you need to disable the Enable Load option for each of the edited tables in the Query Editor.
To do so, right-click on a table and uncheck Enable Load.
When you disable the load of a table, its table name will be italicized.
Refresh Time In Power BI Results
When you perform this technique, you’ll be able to reduce the amount of time it takes to refresh tables and data in Power Query.
In this example, the Refresh History of the referenced data shows refresh times between 5-10 minutes. Also, none of these refreshes failed.
On the other hand, if you look at the Refresh History before the tables were referenced, you can see that schedule refreshes were failing between 3 to 4 times a day.
***** Related Links *****
Last Refresh Date & Time In Power BI – Updated Solution
Refresh Excel File In Sharepoint W/ Power Automate Desktop
Power BI Report Refresh Automatically | PBI Service
Conclusion
In Microsoft Power Query, you can set the refresh frequency for a query to ensure that the data is up-to-date. This can be useful if you’re working with data that changes frequently and you want to ensure that you’re always using the most current data.
However, errors can occur when trying to refresh a query for a variety of reasons. One of the easiest ways to prevent errors and improve refresh time is by referencing your tables in Power Query. It’s a highly recommended practice to apply in all of your existing Power BI models.
All the best,
Jarrett Moore
