#PowerQuery makes advanced VLOOKUP so easy!

Do you use VLOOKUP?  If you do, you should know there are limitations of VLOOKUP.  Also there are some cases VLOOKUP is not straightforward and efficient.

Consider the following cases:Excel Tip - Complicated vlookup with PQ

What we want to achieve is to map the corresponding staff information (in lookup table, named tb_StaffInfo) to the orange tables (named as tb_Employee, tb_Name, tb_Dept) on the left.

The challenge

Case 1: Although there is a common key (EmployeeID vs. Staff ID) in both tables, the lookup direction for Name is to the left which an ordinary VLOOKUP fails to accompanish. Read here to learn more this limitation and an alternative way to overcome this.

Case 2: There is no common key, we may need to combine First Name and Last Name in tb_Name first to get the common key; or to split the Full Name in the lookup table into First and Last Names, then do VLOOKUP 2 values.  Either way, it’s not direct.

Case 3: Duplicate record (Dept) in the lookup table.  We know that VLOOKUP will only return the first match in the lookup table.  VLOOKUP cannot return all the matching records in the lookup table.

Power Query, however, overcomes these challenges with ease.

You may download a Sample FIle – Doing complicated vlookup with PQ (Start) to follow along.

Note: All screenshots are prepared using Excel 365, which Power Query is renamed as Get and Transform, under the Data tab.  If you are using Power Query for Excel 2010 or 2013, you may find the icons at different locations on your Ribbon, where Power Query has its own tab.  Nevertheless, the flow should be more or less the same.

Let’s VLOOKUP the Power Query way

First of all,  load all tables into Power Query

  1.  Select any name in the Table
  2. Go to Data –> Get & Transform Data
  3. From Table/Range

Excel Tip - Complicated vlookup with PQ1.0

Tip: We may need to edit the “Changed Type” here.  Since our dataset is quite simple here, Power Query detects the data type of each columns correctly.

Excel Tip - Complicated vlookup with PQ1.1

Now Close & Load To…  Only Create Connection

Excel Tip - Complicated vlookup with PQ1.2    Excel Tip - Complicated vlookup with PQ1.3

Repeat the above steps to load the other three tables.

Excel Tip - Complicated vlookup with PQ2.3

Now we are ready to VLOOKUP the Power Query way.

Case 1 – VLOOKUP to the left

Challenge: The common key is “EmployeeID” and “StaffID”, but we also want the information “Name” which is on the left hand side of the key.

Case1

Let’s go to the Power Query Editor by double-clicking one of the queries in the Queries & Connections pane:

Excel Tip - Complicated vlookup with PQ2.3

Merge Queries

  1. Select the base query, i.e. tb_Employee” we need
  2. Go to Home Tab
  3. Merge Queries…
  4. … as New

Excel Tip - Complicated vlookup with PQ3.0.

Now the Merge table dialog box opens:

  1. Select the base table tb_Employee
  2. Select the lookup table tb_StaffInfo
  3. Select the common key in base table “EmployeeID”
  4. Select the common key in lookup table “StaffID”
  5. Select Left Outer Join Kind

Excel Tip - Complicated vlookup with PQ3.1.

Once we hit OK, we will see the merged query called Merge1 as follow:

Excel Tip - Complicated vlookup with PQ3.2

Very different from traditional VLOOKUP, we won’t see any lookup values at this point. Instead we see a column called “tb_StaffInfo”, which is basically the name of the query just merged.  And under this column, there is only “Table”… nothing else.

Let’s click into the cell of “Table” and observe (the lower pane):

Excel Tips - Complicated vlookup with PQ3

The “Table” contains all the columns of the matching record (EmployeeID).  What we need is to click the “Double open arrows” icon on the header to get expand the columns:

Excel Tip - Complicated vlookup with PQ3.3

  • Uncheck “Staff ID” as it is essentially the same as the “EmployeeID”.
  • Also uncheck “Use original column name as prefix”

Excel Tip - Complicated vlookup with PQ3.4

Hit OK.  Wahlala…… 4 columns, of which 1 column “Name” was on the left to the common key, were looked up correctly!

Excel Tip - Complicated vlookup with PQ3.5

Now, load it to A1 of Result(1) as Table:

Excel Tip - Complicated vlookup with PQ3.6==>  Excel Tip - Complicated vlookup with PQ3.7

Case 1 solved!  Isn’t it easy?

Excel Tip - Complicated vlookup with PQ3.8

 

Case 2 – VLOOKUP 2 values

Challenge: There is no single common key.  We need to lookup 2 matching values.

Case2

To do this, we may either

1. In the tb_Name query, combine First Name and Last Name into Full Name, so that we have one common key:

Excel Tip - Complicated vlookup with PQ4.1

Excel Tip - Complicated vlookup with PQ4.2

or

2. Split the Name column in the query tb_StaffInfo into First Name and Last Name, so that we have two common keys to do the mapping:

Excel Tip - Complicated vlookup with PQ4.3

Excel Tip - Complicated vlookup with PQ4.4

Double click the header to rename it:

Excel Tip - Complicated vlookup with PQ4.5

Excel Tip - Complicated vlookup with PQ4.6

For the 1st method, the Merge Queries step is the same as showed in Case 1.  No need to repeat here.

Let’s see how to Merge Queries based on two lookup values, i.e. First Name and Last Name in this example:

Merge Queries as New

Excel Tip - Complicated vlookup with PQ4.7

When the Merge Queries dialog box is opened:

  1. Select the base query tb_Name
  2. Select the lookup query tb_StaffInfo
  3. Click First Name under tb_Name
  4. Click Last Name under tb_Name
  5. Click First Name under tb_StaffInfo
  6. Click Last Name under tb_StaffIno
  7. Select Left Outer Join Kind

Note: the sequence in steps 3-6 are crucial.  You will see the tiny number next to the headers that shows you the sequence of selection.

Excel Tip - Complicated vlookup with PQ4.8

8. Hit OK

Now the queries are merged as Merge2.  Click the “Double open arrows” icon to expand the columns needed:

Excel Tip - Complicated vlookup with PQ4.9

Uncheck fields that we don’t want.  Put it in other words, select fields we need:

Excel Tip - Complicated vlookup with PQ5.0

Load the result to a A1 of Result(2) as Table:

Excel Tip - Complicated vlookup with PQ5.1

Here we go!  Case 2 solved.

Information is mapped based on 2 values:Excel Tip - Complicated vlookup with PQ5.2

 

Case 3 – Get all matching records from lookup table

Casse3

Challenge: We want to get all information for Department A, B, C that are listed in tb_Dept, from the lookup table tb_StaffInfo.

There are 4, 3 and 3 records of Department A, B and C respectively.  VLOOKUP cannot do it.  No way it can… 😦

Imagine how we do it with regular Excel (assuming no VBA).  We would probably need Advanced Filter to get all matching records from tb_StaffInfo, then map the “Cost Ctr” back to the filtered result… Quite tedious, not to mention our data is so simple here for demonstration purpose.  What if we have 10 columns in tb_Dept… Can’t imagine.  😦

Let Power Query do the Magic

If you have been following along, you should know the steps of Merging Queries well.  So just repeat the Merge Queries steps to merge the query tb_Dept with tb_StaffInfo:

Excel Tip - Complicated vlookup with PQ6.0

Excel Tip - Complicated vlookup with PQ6.1

Excel Tip - Complicated vlookup with PQ6.2

Up to this point, let’s see what do we have for each “Table” in the Merge3 query.

Excel Tips - Complicated vlookup with PQ6

Do you see the difference?  Before, when there is no duplicates on the matching key in the lookup table, there was only one row returned for each table.  Now for Department A which we have four records in the lookup table, the merged Table contains all four records.  Same for Department B and C, where the Table contains 3 and 3 records respectively.

Let’s click the magical “Double open arrows” to expand the fields we need:

Excel Tip - Complicated vlookup with PQ6.3

Three rows of data turns into 10 rows, showing all records for Department A to C.Excel Tip - Complicated vlookup with PQ6.4

Let’s sort the column Department to make it good looking:Excel Tip - Complicated vlookup with PQ6.5

Now, let’s load it to A1 of Result(3) as Table:

Excel Tip - Complicated vlookup with PQ6.6

Here we go!  Case 3 solved with ease!

Excel Tip - Complicated vlookup with PQ6.7

 

Don’t forget, what makes Power Query so amazing is its ability to do the whole transformation with a single click of Refresh when new data comes.  See below screencast in action:

Excel Tips - Complicated vlookup with PQ End

 

Simply awesome.  🙂

Do you feel the Power?

You may download the Sample FIle – Doing complicated vlookup with PQ (Finished) with the queries to view the steps.

Advertisements
This entry was posted in Power Query and tagged , . Bookmark the permalink.

Comments, suggestions, corrections are welcome.

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.