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 UserHi NikTheRussian,
Further to rohit1991's answer. Which is the correct suggestion, to push it back to the source if that is an option.
If that's not possible, this resource may be useful to ensure you are using foldable functions.
Greatly appreciate this - thank you