Selectively remove duplicate rows?

Please note 1 important phenomenon (or bug if you like) when first sorting a table and then removing duplicates: you need to buffer the table after the sort, before removing duplicates, otherwise records may be removed without taking into account the sort order.

Sorted = Table.Sort(sort code)

Buffered = Table.Buffer(Sorted)

RemovedDuplicates = Table.Distinct(Buffered, equationCriteria)

or:

Sorted = Table.Buffer(Table.Sort(sort code))

RemovedDuplicates = Table.Distinct(Sorted, equationCriteria)

Parameter equationCritera may include field names and/or comparer functions.

With comparer functions you can ignore the case and/or use a culture code: e.g. "æ" and "ae" are considered equal in some cultures.

The parameter can take on various formats as illustrated in the examples below.

let
    Source = #table(type table[Text1 = text, Text2 = text],{{"a", "Encyclopaedia"},{"A","Encyclopædia"}}),

// ****   Format equationCriteria: comparer function
    // Result: both records ("a" and "A" are not equal):
    RemovedDuplicates1 = Table.Distinct(Source, Comparer.FromCulture("en-US")),

    // Result: 1 record (æ and ae are considered equal in "en-US"; true = ignore case))
    RemovedDuplicates2 = Table.Distinct(Source, Comparer.FromCulture("en-US", true)),

    // Result: 2 records (æ and ae are not considered equal in "da-DK")
    RemovedDuplicates3 = Table.Distinct(Source, Comparer.FromCulture("da-DK", true)),

    Indexed = Table.AddIndexColumn(RemovedDuplicates2, "Index", 0, 1),

// ****   Format equationCriteria: (list of) field name(s) (as generated from the user interface if columns were selected):
    RemovedDuplicates4 = Table.Distinct(Indexed, "Text1"),
    RemovedDuplicates5 = Table.Distinct(Indexed, {"Text1"}),
    RemovedDuplicates6 = Table.Distinct(Indexed, {"Text1","Text2"}),

// ****   Format equationCriteria: list of field name with comparer function:
    RemovedDuplicates7 = Table.Distinct(Indexed, {"Text1", Comparer.OrdinalIgnoreCase}),

// ****   Format equationCriteria: list of lists with field name and comparer function:
    RemovedDuplicates8 = Table.Distinct(Indexed, {{"Text1", Comparer.OrdinalIgnoreCase},{"Text2",Comparer.FromCulture("en-US")}})

in
    RemovedDuplicates8