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

Ok, this is pretty weird.

I ran the tests again, but instead of reordering the table using Table.Sort I just entered the data in a different order from the start.

Here are the results:

(For some reason I can't embed images even though I had no such problem yesterday. Sorry if this is harder to follow or the images eventually go down)

Source

Distinct

Reordered Source

Reordered Distinct

Code:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WclLSUUpUitWJVnJEYSUpxcYCAA==", 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.Ascending}}),
    ReorderedExampleResult = Table.Distinct(ReorderedExample, {"b"})
in
    ReorderedExampleResult

We can easily see that order is indeed important, but that for some reason reordering the table using Table.Sort doesn't make a difference. I found that if we need to, we can use Table.Buffer to make Table.Distinct take into account the changes made by Table.Sort. Here's my third and final test:

Source

Distinct

Reordered Source

Reordered Distinct

Code:

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.Buffer( Table.Sort(Example,{{"a", Order.Descending}}) ),
    ReorderedExampleResult = Table.Distinct(ReorderedExample, {"b"})
in
    ReorderedExampleResult

Hi Belin ,
did you buffer the sort operation? (see here:  Bug warning for Table.Sort and removing duplicates... - Microsoft Power BI Community )

Here's another article about this topic you might find helpful.

https://docs.microsoft.com/en-us/power-query/commonissues#preserving-sort

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

You are forgetting that if you want to remove distinct letters that are different cases (A/a) then you need to add the optional parameter Comparer.OrdinalIgnoreCase

Table.Distinct(Table, {{“b”,Comparer.OrdinalIgnoreCase}})

This will treat A and a as duplicates. Otherwise, A/a are not the same in this function. 
--Nate

[SOLUTION]

After googling a lot, I fount this site explaining exactly what happened, and how to solve.

It’s simple, you just need add the buffer of the column you want to sort.

just add this step: 

= Table.Buffer(Table.Sort( #"Linhas Classificadas",{{"data_lancamento", Order.Descending}}))

Source: https://exceleratorbi.com.au/remove-duplicates-keep-last-record-power-query/

I just found out the hard way.

the algorithm goies top to bottom, so I having a set like this

number updated_on

inc0001 01/01/22

inc0001 02/01/22

would remove the second occurrence from the top.

I wanted to keep the most updated record, so i sorted the data first, but still the algorithm remove the same  record, ignoring any sorting

At the end I'm 99% positive that eliminate from top to bottom every reoccurrence...hoping that is not using a something faster and more difficult to predict and control.

In the past the same function in excel worked this way, so I hope is still the same

Cheers