Distinct column/table where value in column DateIn is oldest date

Hi CornerACK

In your Query editor, you can use that code (you just need to customize the source or just to start from the lines you need, such as Removed Columns).

let
    Source = Excel.Workbook(File.Contents(" your source "), null, true),
    Sheet1_Sheet = Source{[Item="Sheet1",Kind="Sheet"]}[Data],
    #"Promoted Headers" = Table.PromoteHeaders(Sheet1_Sheet, [PromoteAllScalars=true]),
    #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"ClientId", Int64.Type}, {"GenderId", type text}, {"AgeId", Int64.Type}, {"BirthDate", Int64.Type}, {"CityId", Int64.Type}, {"ProductCodeId", type text}, {"DateIn", Int64.Type}}),
    #"Removed Columns" = Table.RemoveColumns(#"Changed Type",{"GenderId", "AgeId", "CityId", "ProductCodeId"}),
    #"ID - adding" = Table.AddColumn(#"Removed Columns", "ID", each Text.Combine({Text.From([ClientId], "fr-FR"), Text.From([DateIn], "fr-FR")}, "_"), type text),
    #"ID - sort" = Table.Sort(#"ID - adding",{{"ID", Order.Ascending}}),
    #"Index 1 - adding" = Table.AddIndexColumn(#"ID - sort", "Index", 1, 1),
    #"Index 2 - adding" = Table.AddIndexColumn(#"Index 1 - adding", "Index.1", 2, 1),
    #"Merged Queries - index 1 & Index 2" = Table.NestedJoin(#"Index 2 - adding",{"Index.1"},#"Index 2 - adding",{"Index"},"Index 2 - adding",JoinKind.LeftOuter),
    #"Merged Queries - expande ClientID" = Table.ExpandTableColumn(#"Merged Queries - index 1 & Index 2", "Index 2 - adding", {"ClientId"}, {"Expanded ClientID"}),
    #"OLDEST DATEIN - adding" = Table.AddColumn(#"Merged Queries - expande ClientID", "OLDEST DATEIN", each if [ClientId] = [Expanded ClientID] then "NO" else "YES")
in
    #"OLDEST DATEIN - adding"

It is a little bit crasy but it works fine. Indeed, after having sort on the new ID column (concatenation of ClientID ad DateIn), I added 2 indexes to make a Merge Queries inside the same table (as PowerBi is working by line, not by cell, I cheat with double indexes to display the ClientID value from the next line). Then, I just need to add a calculated column saying that => IF ClientID from next line is the same as the current ClientID then display NO else YES.

Regards,

CR