LookUp from Column (Power Query)

Question

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?

Answer 1

Hi @LearnerBeta

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...

Answer 2

@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