Local evaluation of Table.Join or Table.NestedJoin with key equality comparers is not supported

Thankyou, rohit1991, for your response.

Hi RuchiK,

Thank you for your follow up.

Based on my understanding, you are on the right path by applying minimal transformations; however, even lightweight operations such as creating custom columns or handling null values can affect query folding. Query folding is crucial for the effective functioning of DirectQuery and Dataverse within the Power BI Service.

In addition to the response provided by rohit1991, please find below some additional steps that might assist in resolving the issue:

1. In Power Query, right click each step and select View Native Query. If this option is disabled after a particular step, it indicates that query folding is broken, which can prevent joins from being pushed down to Dataverse.
2. Reorder your transformations to preserve query folding. Ensure that steps such as custom column creation (SOW, Start Date, End Date), null replacements, and data type conversions are performed after any foldable steps like joins or column selections.
3. If query folding breaks due to complex business logic, consider implementing calculated columns such as SOW or date logic using DAX, or alternatively, create these as custom fields directly within Dataverse.
4. If full query folding cannot be maintained and there are concerns regarding performance or business logic, please consider switching those specific tables to Import mode.

Additionally, kindly refer to the following links for more information:
Understanding query evaluation and query folding in Power Query - Power Query | Microsoft Learn
Query diagnostics - Power Query | Microsoft Learn
Best practices when working with Power Query - Power Query | Microsoft Learn

We hope the information provided proves helpful in resolving your issue. Should you have any further queries, please feel free to reach out to the Microsoft Fabric community.

Thank you.