LookUp from Column (Power Query)

LearnerBeta 21 Reputation points
2020-11-07T10:49:56.25+00:00

I have two columns, Emp and Manager and would like to add the Type column to determine who is Individual Contributor and who is Manager. In excel I use Vlookup but don't see such function in Power Query. Can anyone help me with easiest solution?38124-example.jpg

Not Monitored
Not Monitored
Tag not monitored by Microsoft.
35,519 questions
0 comments No comments
{count} votes

Accepted answer
  1. Lz._ 8,981 Reputation points
    2020-11-07T13:31:06.43+00:00

    Hi @LearnerBeta

    38058-demo.png

    let  
        Source = Table.FromRecords(  
            {  
                [Emp=1, Manager=6], [Emp=2, Manager=7], [Emp=3, Manager=1],  
                [Emp=4, Manager=8], [Emp=5, Manager=3], [Emp=6, Manager=1]  
            },  
            type table[Emp=Int64.Type, Manager=Int64.Type]  
        ),  
        EmpList = List.Buffer(Source[Emp]),  
        Type = Table.AddColumn(Source, "Type", each  
            if List.Contains(EmpList, [Manager])  
            then "Manager"  
            else "Individual Contributor",  
            type text  
        )  
    in  
        Type  
    

    If this solves your problem please mark this reply as answer to help others with a similar issue. Thanks in advance Any question please let me know + Nice day...

    0 comments No comments

1 additional answer

Sort by: Most helpful
  1. Lz._ 8,981 Reputation points
    2020-11-07T16:18:38.2+00:00

    @LearnerBeta

    Buffering (List.Buffer in the previous option) is sometimes counterproductive (i.e. with very large Table/List). So, if you experience performance issue with the previous option, test the following:

    let  
        Source = Table.FromRecords(  
            {  
                [Emp=1, Manager=6], [Emp=2, Manager=7], [Emp=3, Manager=1],  
                [Emp=4, Manager=8], [Emp=5, Manager=3], [Emp=6, Manager=1]  
            },  
            type table[Emp=Int64.Type, Manager=Int64.Type]  
        ),  
        MgrTable = Table.SelectColumns(Source, "Manager"),  
        Joined = Table.NestedJoin(Source,"Emp", MgrTable,"Manager", "Match", JoinKind.LeftOuter),  
        Type = Table.AddColumn(Joined, "Type", each  
            if Table.IsEmpty([Match])  
            then "Individual Contributor"  
            else "Manager",  
            type text  
        ),  
        RemovedMatch = Table.RemoveColumns(Type,{"Match"})  
    in  
        RemovedMatch  
    

    If this solves your problem please mark this reply as answer to help others with a similar issue. Thanks in advance Any question please let me know