Functions like VLOOKUP and XLOOKUP are essential for consolidating data sets in Power Query. There are a number of powerful features of Power Query that let you combine data. We’ll explain how to replicate the effect of these two popular Excel formulas using the Power Query interface.
Before diving into formulas and functions of Power Query it’s important to have a quick understanding of what Power Query is, how it’s still relevant today, and most importantly how working with it differs from working with regular Excel spreadsheet formulas.
Let’s jump in!
Table of Contents
A Brief Introduction to Excel Power Query
Power Query is an advanced data prep and transformation tool. It’s available in Excel, and other Microsoft Products such as Power BI, Data Flows, and Data Wrangler making it as relevant today as it was when it was first released in 2010. Power Query enables Excel users to automate the creation of their spreadsheets, connect directly to numerous data sources and work with data sets that would otherwise be too large or complex for regular Excel formulas.
Power Query Works with Columns and Tables
At the heart of Power Query’s functionality is its ability to work intuitively with columns and tables. Unlike traditional Excel formulas that operate on a cell-by-cell basis. This approach allows for more repeatable and scalable data transformations and aggregations.
It also represents one of the biggest challenges for people new to Power Query coming from the Excel world.
Power Query has its own interface with buttons for many common tasks. Users can filter, sort, merge, and pivot data with just a few clicks. The columnar and tabular view nature of Power Query makes handling of data bulk easier, Operations like removing duplicates, looking up data from other sources, or filtering possible for an entire data table at a time vs. having to create multiple Excel Formulas.
How to VLOOKUP in Power Query
While Power Query does not use VLOOKUP by name, it offers similar functionality through its “Merge Queries” feature. This allows you to combine tables based on a common key, effectively replicating VLOOKUP and XLOOKUP functionality.
- Open Power Query: Access Power Query Editor by selecting “Get Data” in Excel.
- Merge Queries: Use the “Merge Queries” option, selecting the primary table and the table you want to look up data from.
- Select Key Columns: Choose the columns in both tables that you want to match on.
- Expand the Table: After merging, expand the new column to include the data you need from the second table.
And here is how to perform the same steps in more detail.
Importing Data into Power Query
Power Query is a feature set inside of Excel that has a different user interface, and because it works with data on a table or column basis you have to import your data into Power Query in a tabular format.
Select a table of data in Excel, and go to Data, From Table/Range or connect to an external data source.
Once it’s loaded into Power Query, click Close & Load to save it, then do the same thing with the second dataset you want to lookup.
Don’t worry about importing limiting the columns you’re importing. You’ll import the entire table then select the columns you want to return as part of the VLOOKUP or XLOOKUP at a later step.
In our Example, we’ve imported to tables, one for Sales and one for Customers.
Tip: Double click on a table name 0n the left side of Power Query to rename and keep track of which table is which..
In the screenshot below the top half shows two Excel Tables, and the bottom half shows two tables that have been imported and renamed inside of the Power Query View.
Use the “Merge Queries” feature, selecting the appropriate join kind to match your data retrieval needs, such as “Inner” (like VLOOKUP) or “Left Outer” (like VLOOKUP with an IFERROR).
Merge Two Power Query Tables to Re-create a VLOOKUP
Once multiple data tables are loaded into Power Query, we can Merge them based on columns of common values. Start by selecting a starting table, this is the primary data set that you want to perform the lookup on. Go to “Home” and select “Merge Queries”. Select the Common Data Column and Press OK.
When Merging Queries in Power Query there are two options. The standard Merge Queries which will combine data from the second table with the first selected table. There is also Merge Queries as New which leaves the first two tables as they are, and then creates a third table with all columns combined. It can be useful if you want to keep the source data intact for future processing or reference.
Configuring Power Query Merge Settings
The Merge Dialog box in Power Query lets you configure several things. The first and most important are the column or columns that two tables have in common to combine them based off of. The top table shown will be the table you had selected in Power Query when you pressed the “Merge Queries” button.
The second table at the bottom is selected from the dropdown menu below the first data table.
To select the common columns in Power Query, left click on each common column from two tables. When they highlight green they are considered in the column matching criteria.
You can tell Power Query to consider values from multiple columns by holding down CTRL while left-clicking to select multiple columns. It’s a big-time saving feature to avoid having to concatenate a primary key like you would in standard Excel spreadsheets.
The next major setting is the Join Kind.
Configuring Power Query Merge Join Kinds
At the bottom of the Power Query Merge editor, the Join Kind dropdown box shows you the multiple ways you can combine data sets. The most common will be Left Outer Join which re-creates the effect of a VLOOKUP where it finds the values that it can and returns empty on the others.
You can learn more about the different join types, what they do and when to use them in Our Definitive guide on Merging Data in Power Query.
Press OK to close and save the Merge Queries Settings
Expanding Merged Queries
Power Query merges tables in a collapsed view. After completing a merge, a new column appears at the right that has the word Table repeating. This is a collapsed view of the second table. Click the Two Arrows on the right side of the column header to expand the table.
A filtering box appears giving you the option to choose which columns to show in an expanded view. You can also choose to disable “Use original column name as prefix’ which is turned on by default. This setting will pre-pend table names to more easily keep track of where data came from, but usually you end up renaming the columns for readability later.
After selecting the column you want to VLOOKUP, click OK and the results will be a new column with data from a second data table added as a column to your initial Excel Data.
Pressing Close & Load in the Power Query Editor will load the new combined data back into an Excel sheet or other location as selected.
XLOOKUP in Power Query
XLOOKUP, Excel’s more flexible successor to VLOOKUP, does not have a direct counterpart in Power Query. However, “Merge Queries” provides a similar level of functionality by allowing you to specify which columns to match and how to handle missing data.
A benefit of using Power Query Merge Queries function is that it’s agnostic of where columns appear within a second dataset. There is no need to worry about using VLOOKUP vs XLOOKUP when the desired result column is to the left or to the right.
VLOOKUP in Power Pivot DAX
Another Excel feature, Power Pivot uses Data Analysis Expressions (DAX) for data manipulation. The VLOOKUP equivalent in DAX is the
RELATED function, which retrieves a value from another table related by a common column. DAX and Power Pivot are less commonly used in Excel, but are widely used within Microsoft Power BI.
When to Use Power Query Merge vs VLOOKUP or XLOOKUP
If you’re wondering when you should use Power Query over a standard VLOOKUP or XLOOKUP, here are some examples of when the technique may prove valuable.
Use Power Query When:
- You need to return values from multiple columns.
- You want to combine a lookup with a filtered view to see only matching records. (Inner-join Query Type)
- You want to see the records that match and don’t match (Modifying Query Types)
- You’re working with very large datasets where performance is a concern.
- Spreadsheet prep is repetitive and time consuming.
For those new to Power Query it can take a bit of getting used to the nuance and interface but after working with it more often you’ll find that many of the desired features are available with buttons and minimal need to write formulas which save even more time!
Whether you’re working in Excel or Power Query, understanding how to effectively perform lookups is key to efficient data transformation. By utilizing Power Query’s Merge Queries function you can re-create the functionality of VLOOKUP and XLOOKUP quickly and efficiently.
Once the Power Query is setup it can a big-time saver because all you need to do is update the incoming data, refresh Power Query and let Excel go through the recorded applied steps to go from raw data to finished data without manual intervention.