Data flow refresh error because of Merged tables (same source)

When you do merges, it helps to "merge as new query" instead of merging into the existing query. I don't know if this is a bug or a feature, but that is the fix even if your source is the same (I have this happen occasionally too). Also run a "refresh all" in the query editor. 

Hi NasreenE,

Thanks for reaching out to the Microsoft fabric community forum. Thank you christinepayton, and Akash_Varuna, for your inputs on this issue.

After thoroughly reviewing the details you provided, I have identified few workarounds that may help resolve the issue. Please follow these steps:

Thank you for your insightful observation regarding query folding issues during merges with SharePoint data sources. You are correct that SharePoint lists support query folding; however, certain transformations, such as complex merges, can disrupt this process, resulting in refresh failures.

To address this issue, you might want to simplify your merge operations or execute them within a Power BI Datamart. This platform offers server-side processing and can manage complex transformations more efficiently.

When merging large datasets, it is advisable to use a "Left Join" instead of a "Full Join" to reduce the number of records processed. Enhancing performance can also be achieved by reducing the number of columns in each table before merging, keeping only the necessary fields.

Furthermore, rather than using a Merge Query, consider using an Intermediate Table by loading both tables as separate entities in the dataflow and performing the merge in Power BI or a downstream process.

If the SharePoint list columns have been renamed or removed, the dataflow may fail during merging. Please manually refresh the preview in Power Query to detect any schema changes. If there are changes in column names, update them in the Merge Queries step.

Kindly refer the below following link for better understanding:
Understanding query evaluation and query folding in Power Query - Power Query | Microsoft Learn
Introduction to DataMart’s - Power BI | Microsoft Learn

Merge queries overview - Power Query | Microsoft Learn

If this post helps, then please give us ‘Kudos’ and consider Accept it as a solution to help the other members find it more quickly.

Thank you.