Table.Distinct incorrect

[mike9999]

Hi-

I'm running a dataflow on a PPU P1 capacity.

I have ~12 million rows.

I'm attempting a Table.Distinct on six columns of various types. I get incorrect distinct results (it removes at least a row that isn't a duplicate).

Table.Distinct(#"Filtered rows", {"Col1-Text", "Col2-Text", "Col3-Text", "Col4-Number", "Col5-Text", "Col6-DateTime"})

Per other threads I tried but this produces an out-of-memory error-

Table.Distinct(Table.Buffer(#"Filtered rows"), {"Col1-Text", "Col2-Text", "Col3-Text", "Col4-Number", "Col5-Text", "Col6-DateTime"})

Any suggestions? I see the recommendation that buffer is used for more consistent results but i don't think that is an option in my case.

[Omid_Motamedise · Accepted solution]

the syntax is correct, try to group the rows based on that 6 columns and see the groups of rows that remoed wrongly this might help to descover the reason of reamoving this rows.

[pbiuseruk · Accepted solution]

The reason you received that suggestion about the Table.Buffer is because sometimes when query folding occurs (when the query is being run at source rather than in the Power BI engine), then it can rearrange steps in order to be more efficient. Sometimes this leads to incorrect results.

Another way to stop this is to use Table.StopFolding instead of the Table.Buffer but I think you may end up getting the same message as they do near enough the same thing.

Let me know if it happens to work for you though

[pbiuseruk]

Also, as another suggestion, could you try to concatenate those columns into one column and then do a distinct count, directly on that one column?

[Omid_Motamedise · Accepted solution]

the syntax is correct, try to group the rows based on that 6 columns and see the groups of rows that remoed wrongly this might help to descover the reason of reamoving this rows.