Quick VLOOKUP in Power Query

VLOOKUPs in Power Query are joins between 2 tables. If you’ve been using Power Query for a while, I’m pretty sure that you know about that.

You can although apply INDEX MATCH styled lookups too, which I think is a quick way of applying VLOOKUP in Power Query. When I say quick – I just mean the ease of application but frankly I haven’t stress tested them on any large data and I don’t recommend anything other than good old styed RELATIONSHIPS in Power BI

With all that said, let’s go check out VLOOKUP in Power Query.

 

VLOOKUP in Power Query

 

Example 1 – Consider this Data!

I want to display the Day Name corresponding to the day number as a new column.

Conventionally, I can apply a Left Outer Join in Power Query by applying a merge or solve this by writing a long IF statement, instead let's explore a quicker way of doing this.

I need two things-

1. The day names as a list – {“Mon”, “Tue”.. and so on}
2. A technique to match the Day Number to the order of the Day Name List.

Let’s create a new custom column with the following M Code

• The curly brackets are the day list
• { [Delivery Day] – 1 } tell me which item to pick from the above list.

Committing to this formula will display the desired output.

Example 2 – A slightly more complex Case

I have a Leave and Employee table.

I want to find the leave count for the employees from the Leave table corresponding to Emp ID in Emp table.

Let’s create a new Custom Column as Leave Count (in the Emp Table)

Referencing the entire table (=Leaves) returns the full table in each row.

Now let’s edit the same custom column to find the position number in Emp ID in the leave count list

I can revise my formula as follows

• If you take a look at the last row of the above formula, I still have the 2 core parts ListName { PositionNo }
• The only difference is that now both the List Name (leavelist) and PostionNo (Positionno) are derived in the previous steps.

Once I commit to the above code, I get the Leave Count.

 

 

More on Power Query

Dynamic Filter by a Range of Values in Power Query

Dynamic Unpivoting in Power Query

Nested LET Statement in Power Query

Dynamic Data Types in Power Query