Forum Discussion
Removing Duplicates (based on 2 or 3 columns) without breaking query folding
The issue occurs because some Power Query steps , like sorting, adding an index, or removing duplicates ,can’t be sent back to the data source. When that happens, query folding stops, and Power Query starts processing the data locally instead of pushing the logic to SQL or Excel.
The best fix is to handle duplicates directly at the data source. If your data is from SQL, write a query using ROW_NUMBER() or RANK() to remove duplicates before loading it into Power BI. This approach keeps query folding active and improves performance.
If your source is Excel or SharePoint, folding isn’t supported, so you’ll need to remove duplicates within Power Query using Table.Distinct or Group By. It might be a bit slower, but that’s expected for these sources.
Super User
The issue occurs because some Power Query steps , like sorting, adding an index, or removing duplicates ,can’t be sent back to the data source. When that happens, query folding stops, and Power Query starts processing the data locally instead of pushing the logic to SQL or Excel.
The best fix is to handle duplicates directly at the data source. If your data is from SQL, write a query using ROW_NUMBER() or RANK() to remove duplicates before loading it into Power BI. This approach keeps query folding active and improves performance.
If your source is Excel or SharePoint, folding isn’t supported, so you’ll need to remove duplicates within Power Query using Table.Distinct or Group By. It might be a bit slower, but that’s expected for these sources.