Forum Discussion

ethanluo's avatar
ethanluo
Frequent Visitor
1 year ago
Solved

Dataflow stuck on refreshing for hours

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...
Dataflow
Need Help
  • v-pnaroju-msft's avatar
    v-pnaroju-msft
    1 year ago

    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's avatar
    v-pnaroju-msft
    1 year ago

    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.

v-pnaroju-msft's avatar
v-pnaroju-msft
Community Support
1 year ago

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's avatar
    SinaF
    New Member
    11 months ago

    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's avatar
      v-pnaroju-msft
      Community Support
      11 months ago

      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:
      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.