VLookup in Power Query

[Anonymous]

Good afternoon. I am working through modifying a vlookup for power query and am runninginto issues.

Goal: Fill in mgr grade_rank with the manager's grade rank. 

Method: Match mgr_emplid and Employee ID columns and return the value in the grade_rank column. 

In the below example, I matched the mgr_emplid "1313" with the Employee ID "1313" and returned the value of "5" to mgr grade_rank. 

I know how to do it in excel, but am struggling to do it in powerquery as Lookup and Match functions. Any help would be appreciated.

Current version: 

Mgr Grade Rank = LOOKUPVALUE(Main[grade_rank],Main[mgr_emplid],Main[Employee ID],0)   Then how do I add the match in and return the value?  Thank you.

[amitchandak]

@Anonymous , refer

https://eriksvensen.wordpress.com/2019/02/28/powerquery-replicate-doing-an-excel-vlookup-in-m/

https://www.myonlinetraininghub.com/excel-power-query-vlookup

[Anonymous]

try to adapt this code

 

[edhans · Accepted solution]

THe easiest way to do this in Power Query is to merge the table with itself, but merge the Manager ID column with the Employee ID coluimn, then expand the grade.

Then expand that field only.

I see your example is using LOOKUPVALUE() - that is a DAX function, and you can use this as a calculated column:

Column = 
LOOKUPVALUE(
    [Grade],
    [EmployeeID],
    [mgrID]
)

It will return this:

But I recommend against it. In general, try to avoid calculated columns. There are times to use them, but it is rare. Getting data out of the source system, creating columns in Power Query, or DAX Measures are usually preferred to calculated columns. See these references:
Calculated Columns vs Measures in DAX
Calculated Columns and Measures in DAX
Storage differences between calculated columns and calculated tables

Avoid the temptation to work in the Data view and create a bunch of calculated columns like you would think of when using Excel when you just add another column. Power BI is very different and that is not a good practice to get into. Try to use Power Query for transformations and bringing in new data, and Measures for data analysis. Avoid Calculated Columns where possible.