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!
Let’s assume you want to create a table by merging Size and Color table together based on the Product ID column:
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:
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”.
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.
Once you hit OK in the previous window, a new query with a new column will appear. Hit the arrows next to “NewColumn”.
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.
You are done with 0 formulas needed! You just need to load the final query to Excel.
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.