Dataflow stuck on refreshing for hours

[ethanluo]

I have a urgent issue with the dataflow refresh in workspace.

Many Dataflows in workspace have been refreshing continuously for several hours (normally it only takes a few minutes) and never stop. I try to cancel it and refresh the dataflow, but it still can't stop for a long time.

How can i fix it permanently?

Any advice would be appreciated.

[v-pnaroju-msft · Accepted solution]

Thankyou, @lbendlin, for your response.

Hi @ethanluo,

We appreciate your inquiry through the Microsoft Fabric Community Forum.

Based on my understanding, if a dataflow is stuck refreshing for hours and cannot be canceled via the user interface, it may be caused by complex queries, large volumes of data, or issues related to source connectivity.

Please follow the steps below, which may assist in resolving the issue:

1. If canceling via the user interface is ineffective, please utilize the REST API.
2. Review the dataflow refresh history, the Power BI Metrics app, and the gateway logs (if applicable) to identify any bottlenecks.
3. Simplify queries by removing unnecessary steps or columns early in Power Query.
4. Split large dataflows by breaking complex flows into smaller segments, and reduce the chaining of dataflows.
5. As an alternative, you may automate the cancellation of long-running dataflows (e.g., those exceeding 2 hours) using Power Automate.

Additionally, please find the relevant links below:
Dataflows - Cancel Dataflow Transaction - REST API (Power BI Power BI REST APIs) | Microsoft Learn
Dataflows - Get Dataflow Transactions - REST API (Power BI Power BI REST APIs) | Microsoft Learn

If you find our response helpful, kindly mark it as the accepted solution and provide kudos. This will assist other community members facing similar queries.

Thank you.

[v-pnaroju-msft]

Hi ethanluo,

We are following up to see if your query has been resolved. Should you have identified a solution, we kindly request you to share it with the community to assist others facing similar issues.

If our response was helpful, please mark it as the accepted solution and provide kudos, as this helps the broader community.

Thank you.

[v-pnaroju-msft]

Hi ethanluo,

We wanted to check in regarding your query, as we have not heard back from you. If you have resolved the issue, sharing the solution with the community would be greatly appreciated and could help others encountering similar challenges.

If you found our response useful, kindly mark it as the accepted solution and provide kudos to guide other members.

Thank you.

[v-pnaroju-msft]

Hi ethanluo,

We have not received a response from you regarding the query and were following up to check if you have found a resolution. If you have identified a solution, we kindly request you to share it with the community, as it may be helpful to others facing a similar issue.

If you find the response helpful, please mark it as the accepted solution and provide kudos, as this will help other members with similar queries.

Thank you.

[v-pnaroju-msft · Accepted solution]

Hi ethanluo,

Thank you for your follow-up.

Based on my understanding, since multiple dataflows(even small ones)are stuck during refresh, and considering that you are using a SQL Server database, the likely cause could be related to gateway performance, configuration, or connectivity issues.

Kindly follow the steps mentioned below, which may help resolve the issue:

1. Verify CPU usage, memory load, and status in Manage Gateways.
2. Ensure that the latest version of the Gateway is installed.
3. Optimize the SQL Server by investigating slow queries, locks, or high resource utilization.
4. Improve Power Query performance by simplifying the transformations.
5. For handling high workloads, consider using a Gateway cluster to enable better load balancing.

If you find our response helpful, please mark it as the accepted solution and provide kudos. This will assist other community members who face similar issues.

Thank you.

[SinaF]

Hi @v-pnaroju-msft  , I am experiencing a similar issue however related to Dataflows stuck "in progress" that use Sharepoint (Excel) files as source. Do you have any ideas how to resolve this issue?

Our dataflows have not been changed for the last 2 years and were running through smoothly before.

[v-pnaroju-msft]

Hi SinaF,

Thank you for contacting the Microsoft Fabric Community Forum.

Based on my understanding, the issue might be due to any of the reasons like expired SharePoint credentials, modifications to SharePoint or Excel connector endpoints, or an increase in the Excel file’s size or complexity over time. When the connection cannot authenticate or when the file takes too long to parse, the refresh may remain In progress instead of completing.

Please follow the steps below which may help to resolve the issue:

1. Go to the dataflow connection settings, re-enter valid SharePoint credentials, and clear any old permissions if necessary.

2. Review the Excel file and remove unused sheets and columns, eliminate formulas, and retain only the required data ranges. Large or complex files can cause refresh delays and timeouts.

3. Apply filters as early as possible in Power Query (M) to reduce the data load.

4. If the dataflow continues to hang, recreate or republish the dataflow, as metadata corruption can sometimes occur.

Additionally, please refer to the following links:

Dataflows Limitations, restrictions and supported connectors and features - Power BI | Microsoft Lea...

Troubleshoot refresh scenarios - Power BI | Microsoft Learn

We hope the information provided helps to resolve the issue. Should you have any further queries, please feel free to contact the Microsoft Fabric Community.

Thank you.

[v-pnaroju-msft · Accepted solution]

Thankyou, @lbendlin, for your response.

Hi @ethanluo,

We appreciate your inquiry through the Microsoft Fabric Community Forum.

Based on my understanding, if a dataflow is stuck refreshing for hours and cannot be canceled via the user interface, it may be caused by complex queries, large volumes of data, or issues related to source connectivity.

Please follow the steps below, which may assist in resolving the issue:

1. If canceling via the user interface is ineffective, please utilize the REST API.
2. Review the dataflow refresh history, the Power BI Metrics app, and the gateway logs (if applicable) to identify any bottlenecks.
3. Simplify queries by removing unnecessary steps or columns early in Power Query.
4. Split large dataflows by breaking complex flows into smaller segments, and reduce the chaining of dataflows.
5. As an alternative, you may automate the cancellation of long-running dataflows (e.g., those exceeding 2 hours) using Power Automate.

Additionally, please find the relevant links below:
Dataflows - Cancel Dataflow Transaction - REST API (Power BI Power BI REST APIs) | Microsoft Learn
Dataflows - Get Dataflow Transactions - REST API (Power BI Power BI REST APIs) | Microsoft Learn

If you find our response helpful, kindly mark it as the accepted solution and provide kudos. This will assist other community members facing similar queries.

Thank you.

[ethanluo]

Thank you for reply!

Now I can manually cancel the data refresh, but what puzzles me is that since yesterday, almost all the dataflows in the same workspce have been stuck in a long refresh process (even those with little data). Could you please tell me what might be causing this? Since my data is stored in a SQL Server database, could it be due to the gateway?

[lbendlin]

If your cancelation request is not honored you need to raise a Priority A ticket. 

Dataflows do not have a timeout limit and can rack up enormous amounts of CUs if not monitored closely.  In our company we have a Power Automate based process that terminates all dataflows running longer than 5 hours (in line with the semantic model refresh limit)

 open a Pro ticket at https://admin.powerplatform.microsoft.com/newsupportticket/powerbi

[ethanluo]

Thank you for reply!

Now I can manually cancel the data refresh, but what puzzles me is that since yesterday, almost all the dataflows in the same workspce have been stuck in a long refresh process (even those with little data). Could you please tell me what might be causing this? Since my data is stored in a SQL Server database, could it be due to the gateway?