Forum Discussion

hash86's avatar
hash86
Frequent Visitor
3 years ago
Solved

VLOOKUP with duplicate values in al column

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 RoleStart Date 
1Cashier16/07/2022
2Supervisor 26/06/2022
2Cashier02/05/2022
3Cashier02/08/2022
3PT Sales Assistant12/03/2022
4FT Sales Assistant 20/02/2022
4PT Sales Assistant31/01/2022
4Cashier04/04/2022

 

 

Output Required 

 

ID Job Role
1Cashier
2Cashier
3PT Sales Assistant
4PT Sales Assistant
Need Help
  • BA_Pete's avatar
    BA_Pete
    3 years ago

    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

3 Replies

  • BA_Pete's avatar
    BA_Pete
    Super User
    3 years ago

    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

  • slorin's avatar
    slorin
    Super User
    3 years ago

    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