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.