In a recent project with a client needing near real-time data, I had to come up with a solution to continuously refresh their semantic model (dataset), with no or very low interval between each run. Leveraging the capabilities of Power Automate, I crafted a flow that utilizes the scheduled ‘Recurrence’ trigger and the ‘Refresh dataset’ action. However, I encountered a common issue related to concurrent refresh requests, which led me to implement error handling techniques to ensure smooth flow execution. This blog outlines my approach in creating the Power Automate flow, addressing challenges, and optimizing data refresh workflows for enhanced efficiency and near-real time data refreshes in import mode.
How to create the Power Automate flow?
- Trigger Setup: The first step in crafting the Power Automate flow was to establish a trigger. Considering the need for continuous refreshes, I opted for a recurrence trigger, scheduling it to run every minute. This ensured that the Power BI datasets would be refreshed frequently, keeping the insights up-to-date.
2. Refresh Dataset Action: With the trigger in place, I proceeded to add the ‘Refresh dataset’ action within the flow. This action, provided by Power BI connectors, initiates the dataset refresh process.
3. Error Handling: Despite setting up the flow for frequent refreshes, I encountered frequent errors in the flow execution. The error message indicated that another refresh request was already in progress, which led to the failure of the flow. To address this issue, I implemented an error handling technique to capture and handle that error.
4. Variable Initialization: To capture and handle that specific error message, I initialized a variable of string type. This variable will store the error message output by the ‘Refresh dataset’ action. I configured the variable initialization to occur only when the Power BI dataset refresh failed, using the ‘Run after’ option.
5. Condition-Based Logic: Following variable initialization, I introduced a condition within the flow that evaluates whether the error message contained the specific error code indicating a concurrent refresh request or if it was another type of error – which can be handled separately. If the condition was met, signifying that another refresh was already in progress, the flow would cancel execution. If that error handling condition is not added in the flow, the flow would fail.
End result
Take a look at the refresh history below and see how the model starts refreshing right after the previous run finishes. Also, note that with a Premium Power BI license, there’s no limit of refreshes you can do per day with Power Automate.
Here’s an overview of the full Power Automate flow:
