Remove duplicates without breaking query folding?

Question

Is there a way to remove duplicates without breaking query folding? I need the folding (for incremental refresh), but I have a ton of duplicates (I go from 3 0378 271 rows to 10 472 after removing duplicates)...

toddmcdermid · Answer

Use a Group By transform instead - that will fold on SQL data sources.

OPTIONAL - If you don't want an aggregate column added to the rowset (so you want it to behave functionally exactly like a remove duplicates), then edit the M that gets generated to remove the aggregate. For example, turn this:

= Table.Group(Source, {"Grouping Column 1","Grouping Column 2"}, {{"Count", each 1, type Int64}})

into this:

= Table.Group(Source, {"Grouping Column 1","Grouping Column 2"}, {})

grggmrtn · Answer

Hi V-lianl-msft thanks for the reply...&nbsp;Your answer would make sense if I had actually set ranges, but I haven't gotten to setting the ranges yet, because query folding on my dataset breaks. The final step in my query (remove duplicates) breaks the folding.&nbsp;So my question is, how can I remove the duplicates without breaking the folding?

justinh · Answer

Never mind. Table.Distinct() only breaks the fold if you use it to remove duplicates from a subset of the tables columns, instead of the whole table. Using Table.Distinct() on the entire table does not break the query folding.

amitchandak · Answer

grggmrtn , how are you deleting duplicate as of now, power Query- delete duplicate ?

grggmrtn · Answer

Yeah, exactly.

Forum Discussion

Remove duplicates without breaking query folding?

7 Replies

Related content

Power Query Folding breaking query

Query folding

Query folding

Removing Duplicates (based on 2 or 3 columns) without breaking query folding

Duplicate ranking