Forum Discussion

pavel_severov's avatar
pavel_severov
Icon for Advocate I rankAdvocate I
6 years ago

Which row will keep Table.Distinct(Table, {"Column"})

Hi colleagues, Which row will keep Table.Distinct(Table, {"Column"}) for the rows with the same values in the "Column": First row? Last row? Random row? MS documentation has no explanation abo...
Need Help
Anonymous's avatar
Anonymous
Not applicable
5 years ago

table.group also appears to have similar behavior to table.distinct.
After all, a possible algorithm to eliminate duplicates in pseudocode could be like this:

Table.Group (tab, "col", each Table.First (_) [all columns but "col"])

 

 

 

 

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUUpUitWJVjJCYSWBWcZwMUOIWCwA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [a = _t, b = _t]),
    Example = Table.TransformColumnTypes(Source,{{"a", type text}, {"b", type text}}),
    #"Raggruppate righe" = Table.Group(Example, {"b"}, {{"all", each Table.First(_)[a]}}),
    Example1 = Table.TransformColumnTypes(Source,{{"a", type text}, {"b", type text}}),
    #"Ordinate righe" = Table.Sort(Example1,{{"a", Order.Descending}}),
    #"Raggruppate righe1" = Table.Group(#"Ordinate righe", {"b"}, {{"all", each List.First(_[a]) }})
in
    #"Raggruppate righe1"

 

 

in the following form the "problem" doesn't arise:

 

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUUpUitWJVjJCYSWBWcZwMUOIWCwA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [a = _t, b = _t]),
    Example = Table.TransformColumnTypes(Source,{{"a", type text}, {"b", type text}}),
    #"Raggruppate righe" = Table.Group(Example, {"b"}, {{"all", each _}}),
    Example1 = Table.TransformColumnTypes(Source,{{"a", type text}, {"b", type text}}),
    #"Ordinate righe" = Table.Sort(Example1,{{"a", Order.Descending}}),
    #"Raggruppate righe1" = Table.Group(#"Ordinate righe", {"b"}, {{"all", each _ }})
in
    #"Raggruppate righe1"

 

 

 

and not even like this

 

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUUpUitWJVjJCYSWBWcZwMUOIWCwA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [a = _t, b = _t]),
    Example = Table.TransformColumnTypes(Source,{{"a", type text}, {"b", type text}}),
    #"Raggruppate righe" = Table.Group(Example, {"b"}, {{"all", each _[a]{0}}}),
    Example1 = Table.TransformColumnTypes(Source,{{"a", type text}, {"b", type text}}),
    #"Ordinate righe" = Table.Sort(Example1,{{"a", Order.Descending}}),
    #"Raggruppate righe1" = Table.Group(#"Ordinate righe", {"b"}, {{"all", each _[a]{0} }})
in
    #"Raggruppate righe1"

 

Anonymous's avatar
Anonymous
Not applicable
5 years ago

About table.sort ...

Suppose you have this table:

 

 

if you sort by column a, you get this:

 

 

but even this different situation would have been valid (third and fourth row are reversed):

 

 

 

This, I believe, depends on the internal algorithm used to do the sorting.

 

If, as plausible as it may be, the table.distinct algorithm first does a sort of rows to "group" the rows that have duplicate values in the control columns, the ordering of the remaining columns, as we have seen, is not determined.

So if the algorithm also takes the first row of the group, it is not necessarily the "first" row you encounter  the source table.