Power BI DirectQuery: Automatically Refresh Less Than Every 15 Minutes

Power BI is an extremely user friendly and flexible analytics, reporting, and dashboarding tool. In the past two years, I’ve witnessed the massive adoption of Power BI by my clients and in the marketplace, often replacing more expensive tools due to its ease of use and low barrier to entry.

Project Challenge

As with any software, there are certain drawbacks that may limit the use or adoption of Power BI in specific situations. On a recent project, my team was asked to develop a real-time report using a Power BI DirectQuery connection. This report was to be filtered with a slicer for a particular work-center, displayed on a user’s monitor on a manufacturing shop floor, and wasn’t going to be interacted with by that user. Lastly, the data was expected to be refreshed at least once per minute as the manufacturing shop floor will be interacting with the data. Power BI’s data refresh documentation mentions:

But what if my user does not interact with the data? Again, there is an answer to that:

But our requirements specified that the report needed to be updated at least once per minute. There was nothing in the documentation about this. There are several posts on the Power BI community (like this one) that discuss Chrome extensions to auto-F5 to refresh the page. But in this case, the requirements call for the user to choose from a work-center on a slicer and leave the page alone. Refreshing the browser causes the user to lose their slicer selection. This left us having to create a different solution.

Our Solution

When inspecting the Power BI report page, the refresh button on the Power BI service could be clicked to refresh the data without impacting the selected slicers.

I asked a colleague and JavaScript guru, Jose Gonzalez, to look into page elements and create a short JavaScript snippet that would simulate a click of this “Refresh” button every minute. After a few minutes, he provided the following code:

javascript:if(window.autoRefreshInterval) { clearInterval(window.autoRefreshInterval); }; window.autoRefreshInterval = setInterval(function() { jQuery(".refresh").click(); },60000)

The code snippet above can be added as a bookmark with the desired interval (red number in milliseconds) to refresh at the user’s preference. Once the page is set with the right slicers, the user can click the bookmark and the page refreshes automatically at the desired interval. This code also clears out any other “autoRefreshInterval” that is running, so you don’t end up with 15 timers all counting down to refresh your page if the user accidentally clicks the bookmark multiple times.

To add a bookmark, see the instructions below:

Power BI Out-Of-The-Box Solutions

Power BI is an extremely powerful analytics and visualization tool; however, there may be some things you feel are drawbacks that may need a solution that isn’t out of the box functionality. Hopefully, this tip will help you achieve your needs for refreshing a Power BI live report through DirectQuery mode.

Have any other questions about Power BI or other analytics and business intelligence tools? Please contact us at findoutmore@credera.com. We would love to discuss potential solutions with you and hear about your experiences.

• Analytics
• Business Intelligence
• Power BI
• Data Visualization