Forum Discussion

pavel_severov's avatar
pavel_severov
Advocate 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
v-lid-msft's avatar
v-lid-msft
Community Support
6 years ago

Hi pavel_severov ,

 

Sorry for that, but we did not find any logic definition of this function in official document. But you can confirm it by the Example 2 of the function document, it will keep the first row.

 


Best regards,

tmijail's avatar
tmijail
Advocate I
6 years ago

Hey v-lid-msft , I did a quick test and that does not seem to be the case.

 

First, I entered the data from that example

 

 

and used 'Remove duplicates' on b

 

 

This is what we expected from the documentation and from the assumption that it keeps the first row.

Now to the real test. I altered the order of the first table so that it looks like this:

 

 

Under the assumption that it keeps the first row, we expect to get the following table:

 

ab
Ba
Ab

 

but instead we get this:

 

 

Here's the code for the tests in case anyone wants to check

 

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WclTSUUpUitWJVnKCs0BiSUqxsQA=", 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}}),
    ExampleResult = Table.Distinct(Example, {"b"}),
    ReorderedExample = Table.Sort(Example,{{"a", Order.Descending}}),
    ReorderedExampleResult = Table.Distinct(ReorderedExample, {"b"})
in
    ReorderedExampleResult