Append Query failing in Gen2 Dataflow (CI/CD)

[Hoping]

I have a query inside a dataflow bringing in 2 years worth of data which is roughly 50 million rows.  I have another query bringing another 2 years worth of data which is bringing in another 2 years worth of data. I am then appending them into 100 million rows but append keeps failg within a minute of starting.

I read some other posts which advised to check the option - Allow combining data from multiple sources but even after this, append is still failing.

Any advise on why this is failing? Is this something to do with the available memory?

[V-yubandi-msft · Accepted solution]

Hi @Hoping ,

Thank you for confirming that Allow combining data from multiple sources  is enabled. Since the append is still not working, it could be related to the volume of data being processed.

Here are some steps you can try that have helped others

1. Reduce the size of each query by filtering or summarizing data before the append step.

2. Instead of combining all data at once, break the process into separate dataflows or queries and merge them afterward.

3. Keep the append step simple handle complex transformations before or after combining data.

4. Use the Monitoring Center to check memory or capacity usage. Spikes during the append may indicate system limits.

For reference, Microsoft official guide on which helps avoid overloading the mashup engine during large operations like appends.

Creating a dataflow - Power BI | Microsoft Learn

regards&thanks
Yugandhar.

[rohit1991 · Accepted solution]

Hi @Hoping ,

Here’s what I’d recommend:

1. Filter or reduce data before appending: Only pull in the columns and rows you absolutely need. The smaller each table is before the append, the more likely it’ll succeed.
   
   

2. Break up the process: Instead of one huge append step, load your sources into separate dataflows or staging tables, then merge them in a later dataflow. Sometimes splitting the workload across steps makes all the difference.
   
   

3. Keep transformations simple before append: Avoid complex steps like joins or calculated columns before the append, do them after, or (if possible) in your source queries.
   
   

4. Monitor capacity and memory: Use the Monitoring Center in Power BI to check for any spikes or resource issues during refresh. If you see failures or spikes at the append, it’s usually a capacity/memory bottleneck.
   
   

5. Test with a smaller sample: Try running the same logic with a much smaller slice of your data. If it works, that’s a sure sign you’re just hitting resource limits at full scale.
   
   

6. If it’s still failing: Try breaking the data into even smaller batches. Or, pre-aggregate/prepare the data outside Power BI (using tools like Azure Data Factory or SQL), and then import the final result.
   
   

[V-yubandi-msft]

Hi @Hoping ,

We would appreciate it if you could let us know whether your concern has been resolved or if you still require assistance. Your feedback may also be useful to others facing similar situations.

Thank You.

[V-yubandi-msft]

Hello @Hoping ,

If your issue is resolved, that's good. If you need any further assistance or additional details, feel free to let me know.

Thank you.

[HarishKM · Accepted solution]

@Hoping Hey,

The failure in appending large datasets in Power BI dataflows is likely due to memory and performance constraints. Power BI has limits on data transformation operations, such as appends, especially with massive datasets like 100 million rows. To resolve this:

1. Reduce the amount of data processed at once by filtering or aggregating data before appending.

2.  Set up incremental data refresh in Power BI to manage large datasets more effectively, allowing smaller chunks of data to be processed at a time.

3.  For extensive datasets, consider using Power BI Premium for enhanced capacity and optimized performance.

Thanks

Harish KM

If these steps help resolve your issue, your acknowledgment would be greatly appreciated.

[V-yubandi-msft]

Hi @Hoping ,

Could you review my response and let me know if it resolves your issue? @rohit1991 , has also provided a solution that may be helpful, so please check that as well. If you need any more clarification or support, feel free to ask.

Best regards,

Yugandhar.

[rohit1991 · Accepted solution]

Hi @Hoping ,

Here’s what I’d recommend:

1. Filter or reduce data before appending: Only pull in the columns and rows you absolutely need. The smaller each table is before the append, the more likely it’ll succeed.
   
   

2. Break up the process: Instead of one huge append step, load your sources into separate dataflows or staging tables, then merge them in a later dataflow. Sometimes splitting the workload across steps makes all the difference.
   
   

3. Keep transformations simple before append: Avoid complex steps like joins or calculated columns before the append, do them after, or (if possible) in your source queries.
   
   

4. Monitor capacity and memory: Use the Monitoring Center in Power BI to check for any spikes or resource issues during refresh. If you see failures or spikes at the append, it’s usually a capacity/memory bottleneck.
   
   

5. Test with a smaller sample: Try running the same logic with a much smaller slice of your data. If it works, that’s a sure sign you’re just hitting resource limits at full scale.
   
   

6. If it’s still failing: Try breaking the data into even smaller batches. Or, pre-aggregate/prepare the data outside Power BI (using tools like Azure Data Factory or SQL), and then import the final result.
   
   

[V-yubandi-msft · Accepted solution]

Hi @Hoping ,

Thank you for confirming that Allow combining data from multiple sources  is enabled. Since the append is still not working, it could be related to the volume of data being processed.

Here are some steps you can try that have helped others

1. Reduce the size of each query by filtering or summarizing data before the append step.

2. Instead of combining all data at once, break the process into separate dataflows or queries and merge them afterward.

3. Keep the append step simple handle complex transformations before or after combining data.

4. Use the Monitoring Center to check memory or capacity usage. Spikes during the append may indicate system limits.

For reference, Microsoft official guide on which helps avoid overloading the mashup engine during large operations like appends.

Creating a dataflow - Power BI | Microsoft Learn

regards&thanks
Yugandhar.