Problems with Table.Distinct

[fedeleu]

Hi!

I have the following code

let

    Origen = Table.Combine({Unico2020, Unico2021}),

    #"Filas filtradas" = Table.SelectRows(Origen, each ([Titolare] = "Empresa")),

    #"Tipo cambiado" = Table.TransformColumnTypes(#"Filas filtradas",{{"Inizio Coll", type date}}),

    #"Columna condicional agregada" = Table.AddColumn(#"Tipo cambiado", "orden_estado", each if [Stato] = "CONSUNTIVATO" then 1 else if [Stato] = "VERIFICA DOCUMENTALE" then 2 else 3),

    #"Filas ordenadas" = Table.Sort(#"Columna condicional agregada",{{"Inizio Coll", Order.Ascending}, {"orden_estado", Order.Ascending}, {"Avviso", Order.Ascending}}),

    #"Duplicados quitados" = Table.Distinct(#"Filas ordenadas", {"Fornitore"})

in

    #"Duplicados quitados"

I expected to keep the first unique value of the column "Fornitore", since I previously ordered by "Inizio Coll", "orden_estado" and "Avviso", which are the fields that give me a priority in the data to keep.

However I have come across records that do not meet that criteria.

Can someone tell me that I am doing wrong that I do not have the first record that meets my order criteria?

Thanks,

Federico

[KNP · Accepted solution]

Hi @fedeleu,

Can you try adding Table.Buffer(PreviousStep), immediately before the Table.Distinct step?

Let me know if this improves the results for you.

Regards,

Kim 

[fedeleu]

That worked!

Thank you very much!!!

Can you explain me what Table.Buffer does?

[KNP]

It stores the table in memory preventing that step from being evaluated again. Typically used for performance improvements.

I think the reason this is needed in the sorting/distinct scenario is best explained in this post...

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