How to Do VLOOKUP in Power Query?

Sooner or later, everyone needs to match data from one table to another based on a common key. In standard Excel, VLOOKUP function would be used. In Power Query, “Merge” function needs to be used.

So how does “Merge” in Power Query work? Simpler than you think. It is actually simpler than then writing VLOOKUP in standard Excel because as always – there is a button for that in Power Query!

A sample Excel file with applied VlookUp/Merge
function can be downloaded here:  
DOWNLOAD NOW FOR WINDOWS

Let’s assume you want to create a table by merging Size and Color table together based on the Product ID column:

Merge - 1 - Sample Problem

In standard Excel, the syntax would look like =VLOOKUP([Product ID Column],[Either Table],2,false). You don’t to have to worry about formulas in PQ, as I said, there is a button for that.

The scenario: You need to lookup Colors for Sizes based on Product ID. Start by loading both tables (Colors, Sizes) to PQ. Color table:

Merge - 2 - Colors

Size table:

Merge - 3 - Sizes

Once your input tables are loaded, it is time do “Merge” (=VLOOKUP). On the home tab, navigate to “Merge Queries”>”Merge Queries as New”. In case you want to create a new final query, you select Merge Queries as New. In case, you want to look up a column for an already existing query, select “Merge Queries”.

Merge - 4 - Merge Button

New window will pop up. In this window, you need to define common key between the two tables in the first step. In our case, the key is “Product ID” column. The column names do not have to match, it is about the values inside those columns. If you want to form key based on multiple columns, just hold CTRL and start selecting columns. Obviously, in every table, same number of columns must be selected. In the sample below, the key is only 1 column,, so I am selecting only 1 column in each table/query.

Now, it is time to define Join Kind, something which makes “Merge” function in Power Query super powerful. “Left Outer” is similar to VLOOKUP in Excel. Have a look on the other options and read the descriptions. “Inner” is very handy for instances where you want to “filter” data based on multiple columns and multiple rows.  It is essentially a way of performing very advanced filters which would be very difficult to prepare with standard “Filter” function. I suggest you try yourself.

Merge - 5 - Merge Window

Once you hit OK in the previous window, a new query with a new column will appear. Hit the arrows next to “NewColumn”.

Merge - 6 - New Column

Select which columns you want to expand. In our case, we are looking up Color so I am only selecting Color. In case you want to expand more columns, you would simply select the needed columns.

Merge - 7 - Expand

You are done with 0 formulas needed! You just need to load the final query to Excel.

Merge - 7 - Close & Load

Important things to keep in mind:

  • Left Outer merge option is similar to VLOOKUP but not the same!!! In standard Excel, when a match is found, only the first matching value is returned. In Power Query, all the matching rows would be recreated. For example, if we had 2 colors for 1 Product ID, the final query would showing 1 Product ID with two matched colors. This is good in some instances but also bad.
  • Complicated Merges slowdown the queries. If you are doing merge on millions of rows, you may see the dreaded “not enough memory” message.

A sample Excel file with applied VlookUp/Merge
function can be downloaded here:  
DOWNLOAD NOW FOR WINDOWS

***Tip: Have a question? Ask in the comment section below or in this Facebook group. You can also sign up for our free newsletter.***

***Do you feel like you need more help? We can help you with PPC Automation and/or with your reporting under our new project MythicalReports.com here.***

9 thoughts on “How to Do VLOOKUP in Power Query?

  1. This was really helpful but I ran into a problem. When matching a new field to my table, the rows reordered themselves slightly. Is there a way to keep these from shifting order?

    1. Hi Sam.
      absolutely. Before you do the merge step, add index column (there is a button for that). After the merge, reorder based on the index column 🙂

  2. Hello, I had a situation I want to used the left join, but only bring the firts matching row, just like in regular excel this is because I want the latest date of purchase for an item , not all of them, is there any way to do this?

    1. Hi Jesus,
      it’s possible but you need to deduplicate your order table first – you need to order it by item ID, then by order date descending, then do Table.Buffer( ) around you ordering step and then “remove duplicates”.

      DZ

  3. What if you need to do a VLookup equivalent using multiple columns as your lookup value which finds data on a different row in the same table?

    1. Hi Michael,
      I don’t think that’s possible. You can do “vlookup” against the same table but all the values would get “multiplied” and I don’t think you want that, do you?

      DZ

  4. Hi Daniel,
    great article but one question. i have 2 tables. the first is a master and i would like to add some additional info from the second. the thing is that the amount information is in master. when i merge with left outher, i get more rows and of course the amounts are multiplied . So if i had original vallue in one line 100, than if my merge created 5 lines, i haeve now 5 x 100. any idea how to keep the amount only 100 but still keep the 5 rows?

Leave a Reply to Daniel Zrust Cancel reply

Your email address will not be published. Required fields are marked *