Forum Discussion
Which row will keep Table.Distinct(Table, {"Column"})
I believe it keeps the first one. You can do quick try to confirm. You can add a sort step just before that step to keep the one you want.
If this works for you, please mark it as the solution. Kudos are appreciated too. Please let me know if not.
Regards,
Pat
Hi mahoneypatI, I also "believe" in "First row", but it is good to have official confirmation from MS. Isn't it? 🙂
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,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:
a b B a A b 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
Just be aware that if you have to sort your table to define the desired logic, you have to use a Table.Buffer with it, as the sort order might not stick without it (mostly relevant for large tables): Bug warning for Table.Sort and removing duplicates... - Microsoft Power BI Community
