Forum Discussion
Which row will keep Table.Distinct(Table, {"Column"})
it seems as if the sort operation doesn't change the table from the point of view of the table.distinct function and uses the same result as before.(*)
The operations that "change" the table (manual reordering, table.buffer, adding an index column, replacing any value in the table - even if the new value is the same as the old one -, changing the type of a column) do " start " the procedure of the table.distinct function all over again
(*) if between the call to the table.distinct function and the definition of the table to which it is applied, only table.sort functions act, the table distinct function is applied to the original table "saving" the sorting operation which is generally heavy.
Maybe 😁
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"
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.
Hello,
I think this is similar to my issue:
It is intended behaviour - Power query steps are not always performed one-by-one. You can force one-by-one by using Table.Buffer
