Remove duplicates without breaking query folding?

[grggmrtn]

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]

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"}, {})

[amitchandak]

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

[grggmrtn]

Yeah, exactly.

[V-lianl-msft]

Hi @grggmrtn ,

This can happen if the date ranges are set NOT up to as follows: [Date] >= RangeStart and [Date] < RangeEnd. Is your upper bound set to be equal to or less than or just less than?

Refer to this thread:

https://community.powerbi.com/t5/Desktop/Problem-with-incremental-refresh-on-power-bi-premium/td-p/7... 

Best Regards,
Liang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[grggmrtn]

Hi @V-lianl-msft thanks for the reply...

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.

So my question is, how can I remove the duplicates without breaking the folding?

[justinh]

@grggmrtn @amitchandak 

I could use this as well, did you ever find out how to do this? 

Table.Distinct() breaks the fold. How do we tell the servers to return a distinct list after all our previous steps? 

[justinh]

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.