How do you automate refresh in Power Query?

If you’ve landed on this article from a Google search, we hope you also read Part 2 and Part 3 of this Power BI tutorial series to get a full understanding of Power BI’s auto refresh and scheduling functionality.

Nabler is a digital analytics consulting company providing data infrastructure, Business Intelligence, Conversion rate optimization and data science services. You can trust us for your Power BI implementation/migration. Talk to our BI consultants about it today – CLICK HERE

You can also get in touch with us about –

• Datorama Consulting Service
• Tableau Consulting Service

Refresh Power BI manually or set-up a refresh schedule in the Business intelligence tool to get the latest and most relevant data in your reports and dashboards.

You typically use ‘Get data’ to import Big data in BI from a file on a local drive / One drive or connect directly to a database in the cloud in our own organization to create reports and dashboards. But are you sure that the data you are getting, is the latest and the most relevant for you?.

If your answer is no, then you need to update active data replicates based on a regular schedule. It is called data refresh and it is critical for making the right decisions?

Now there is some data, like from a Salesforce or Marketo content pack which is automatically refreshed for you. Also, if you use a live connection or DirectQuery, the Big data is automatically updated. But with an Excel workbook or Power BI desktop file that connects to an external online or on-premises data source, you need to refresh your data manually or setup a refresh schedule in the Business Intelligence tool. A dataset is automatically created in Power BI when you use ‘Get Data’ to connect to and upload data from a content pack, file, or when you connect to a live data source.

So, basically when you refresh data, you are updating the data in the dataset that is stored in Power BI from your data source.

Through a series of 3 articles, we will help you understand the different types of refresh in the Business Intelligence tool and how data refresh in Power BI really works.

Types of refresh

Data refresh in local files and files on OneDrive or SharePoint Online

• Data refresh is supported for Power BI Desktop files and Excel workbooks that connect to external online or on-premises data sources. This only refreshes the data for the dataset within the BI service and does not update your local file.
• Keeping your files on OneDrive, or SharePoint Online, and connecting to them from Power BI, provides a great amount of flexibility. But this flexibility brings a challenge because scheduled refresh for files stored in OneDrive, or SharePoint Online, are different from package refresh.

Power BI Desktop file

Excel WorkBook

Comma separated value (.csv) file on OneDrive or SharePoint Online

Data refresh in live connections and DirectQuery to on-premises data sources

• With the On-premises data gateway, you can issue queries from Power BI to your on-premises data sources.
• There is a direct connection between Power BI and the database, with no need to schedule refresh.
• When connecting to a SQL Service Analysis Services (SSAS) data source using a Live connection, unlike DirectQuery, the Live connection to a SSAS source can run against the cache, even upon loading a report. This behaviour improves load performance for the report.
• When you configure a data source with the On-premises data gateway, you can use that data source as the ‘Scheduled Refresh Option’, instead of using the ‘Personal Gateway’.
• If your dataset is configured for a live or DirectQuery connection, datasets are refreshed approximately each hour or when interaction with the data occurs. You can manually adjust the ‘refresh frequency’ in the ‘Scheduled Cache Refresh’ option in the Power BI service.

Data refresh in the cloud database

• With DirectQuery, there is a direct connection between Power BI and the database in the cloud.
• When you interact with a visualization, queries are sent from the BI tool directly to the database.
• Updated data is then returned, and the visualizations are updated.
• Both the Power BI service and the data source are in the cloud and there is no need for a ‘Personal Gateway’.
• If there is no user interaction in a visualization, data is refreshed automatically approximately every hour.
• You can change that refresh frequency using the ‘Scheduled cache refresh’ option and set the refresh frequency.
• To set the frequency, select the gear icon in the upper right corner of the Power BI service, then select Settings.
• The Settings page appears, where you can select the dataset for which you want to adjust the frequency. On that page, select the Datasets tab along the top.
• Select the dataset, and in the right pane you’ll see a collection of options for that dataset. For the DirectQuery/Live connection, you can set the refresh frequency from 15 minutes to weekly using the associated drop-down menu, as shown in the following image.

Data refresh in real-time dashboards

• Real-time dashboards use the Microsoft Power BI REST API, or Microsoft Stream Analytics, to make sure the data is up-to-date.
• Real time dashboards do not require users to configure refresh.

Coming up in the next article –

Configuring a scheduled refresh in Power BI. And also, which datasets are supported against different gateways for a scheduled refresh within the Business Intelligence tool to give you the best Big Data on your dashboards and reports.

The above refreshes are a ‘full refresh’. There is an incremental refresh which we shall speak about as we proceed.

Can power automate refresh a Power Query?

Can Power Query be automated?

Can power automate refresh data in Excel?

How can I speed up refresh Power Query?