VLOOKUP with duplicate values in al column

Hi hash86 ,

Create a new blank query and paste this over all the default code in Advanced Editor:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUXJOLM7ITC0CsgzN9A3M9Y0MjIyUYnWilYyAQsGlBalFZZnF+UUKQJ4RUIEZqgKEbgMjfQNThKQxhqQFqmRAiEJwYk5qsYJjcXFmcUliXgnICUB1xgh1JkAhNwx1YKcY6APNRFGI1UBjQ30DQ1R1SK4y0QciiGQsAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [ID = _t, #"Job Role" = _t, #"Start Date" = _t]),
    chgTypes = Table.TransformColumnTypes(Source,{{"ID", Int64.Type}, {"Job Role", type text}, {"Start Date", type date}}),

// Relevant steps ----->
    groupAllRows = Table.Group(chgTypes, {"ID"}, {{"data", each _, type table [#"ID"=nullable number, Job Role=nullable text, #"Start Date"=nullable date]}}),
    addMinRecord = Table.AddColumn(groupAllRows, "minRecord", each Table.Min([data], "Start Date")),
    expandMinRecord = Table.ExpandRecordColumn(addMinRecord, "minRecord", {"Job Role", "Start Date"}, {"Job Role", "Start Date"}),
// <----- Relevant steps

    remOthCols = Table.SelectColumns(expandMinRecord,{"ID", "Job Role"})
in
    remOthCols

Summary:

-1- Group the table on [ID] and add an 'All Rows' column.

-2- Pick out the the record that has the lowest [Start Date] from each nested table.

-3- Expand the new recod column reinstating your desired columns.

Example output:

Pete