VLOOKUP with duplicate values in al column

[hash86]

Hello,
I am new to PowerBi and have come across a query where I need some help please.
I have a table with multiple ID and job roles and the start date for that job role.
I am looking to find for each ID, their first job role.
In Excel I can simply sort the start date by Oldest and then do a VLOOKUP on the ID. But I am not sure how this works in Power BI
Any help would be great.
Thank you 🙂

Data 

ID	Job Role	Start Date
1	Cashier	16/07/2022
2	Supervisor	26/06/2022
2	Cashier	02/05/2022
3	Cashier	02/08/2022
3	PT Sales Assistant	12/03/2022
4	FT Sales Assistant	20/02/2022
4	PT Sales Assistant	31/01/2022
4	Cashier	04/04/2022

Output Required 

ID	Job Role
1	Cashier
2	Cashier
3	PT Sales Assistant
4	PT Sales Assistant

[slorin]

Hi

Group by ID and use Table.Min function

Table.Group(Previous_Step, {"ID"}, {{"Job Role", each Table.Min(_,"Start Date")[Job Role], type nullable text}})

Stéphane 

[hash86]

@BA_Pete thank you so much 🙂

[BA_Pete · Accepted solution]

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