CODEWITHSUNDEEP
error-handlingpowerbidatasetrefresh
user2671057
user2671057

Reputation: 1543

How to auto refresh Power BI dataset after failure

I have scheduled query for a dataset in Power BI.

In case of a refresh failure, I want Power BI to "retry" to refresh the data again, up to 5 times.

Is there a way to do it?

Upvotes: 3

Views: 3451

Answers (2)

Saikrishna dugyala
Saikrishna dugyala

Reputation: 16

I have implemented a Wait and Retry Mechanism for Data Refresh in Power BI:

To handle data refresh failures due to data source unavailability, I developed a function using M code in Power BI that incorporates a wait-and-retry mechanism. This solution ensures that the data refresh process can attempt multiple retries before finally failing, which increases the robustness of your data import process.

let
get_data = (counter as number) =>
let
output =
try
let
// Replace this block with your specific data source connection logic
Source = YourDataSourceFunction(),
Data =
let
// Replace this block with your specific data processing logic
ProcessedData = Source[YourData]
in
ProcessedData
in
Data
otherwise if counter < 4 then
Function.InvokeAfter(() => @get_data(counter + 1), #duration(0,0,0,20))
else Error.Record("Dataset refresh failure after multiple attempts", "Data source error", "Additional details need to check")
in
output
in
get_data

Explanation:

  • Function Definition: get_data is a recursive function that takes a counter as an argument.

  • Try Block: Attempts to load the data from the specified data source.

  • Catch Block: If an error occurs and the counter is less than 4, the function waits for 20 seconds before retrying. If the counter reaches 4, it records an error message indicating a refresh failure.

  • Usage: To use this function, replace YourDataSourceFunction and YourData with the actual function and data processing logic for your specific data source. You can then call this function with an initial counter value of 0.

Upvotes: 0

Sizzling_Boots
Sizzling_Boots

Reputation: 88

For the time being it doesn't seem possible as confirmed by this post. You can play with the "Command time out in minutes(optional)" in your query when creating your data source as noted in the comments.

enter image description here



If the timeout is left blank the default is 10 minutes. So if the issue is that your queries are timing out this may be the solve for you.

Another workaround is that you can schedule your data source to update multiple times at half hour increments. Like so. Note that depending on how big your data set is this may place a burden on the server you are pulling from. If that is the case then looking into incremental refresh would be your next go to.

enter image description here

Hope this helps.

Upvotes: 4

Related Questions