Unlock the power of data analysis with our comprehensive guide on how to use VLOOKUP-like functions in Power BI. While VLOOKUP has been a staple of building Excel spreadsheets, there is no direct replacement for the functionality of it in Power BI.
We’ll explore a few different approaches that users can take. There are pros and cons to each one and each offers a slightly different result.
Is there a VLOOKUP in Power BI?
There is no exact replacement for an Excel VLOOKUP formula in Power BI. To VLOOKUP in Power BI users can either “merge” two tables together, build a relationship between tables or use the LOOKUPVALUE DAX function.
In all of our VLOOKUP examples we will use the following dataset. Each of these sections will be imported into Power BI as separate tables. One table is an Item ID with the name of a cookie, and the second table is the sales quantity and price.
The commonality between the two datasets is the Item ID# Column.
After importing both tables into Power BI, the data will look the following:
In this example, we want to create a single table visual in Power BI that shows Item # Quantity Sold and Sales Price
Re-creating a VLOOKUP in Power BI by Adding a relationship
The first method of re-creating a VLOOKUP is to create a relationship between two tables. While not super intuitive the example below will explain the reason behind this.
Left Table: Item ID #, Quantity Sold and Sales Price are on a single table in a dataset
Right Table: Cookie Name is on a separate table than the other values and causes a table error.
Power BI can’t display the visual because there is no relationship between the two tables.
In Excel, you would use a VLOOKUP formula to combine columns from two different datasets.
Power BI lets you combine datasets by creating relationships.
Follow these steps to create relationships:
Step 1.) Navigate to the relationships screen by clicking the relationship editor button on the left
Step 2.) Drag and Drop the Columns Names that You Want to Match from one Dataset to another.
After you drop the Item ID # column on the other Item ID# column, Power BI will create a relationship between the two tables. This will allow you to add any field or column based on matches in Item ID#.
Power BI will also allow you to add in any other column from the matching data set that you want to work with. Meaning that rather than having to use multiple VLOOKUPS, you can build the relationship once and it will automatically match all of the data based on common row values.
Once the relationship is established, it will look like this:
Navigate back to the dashboard by clicking the Report View button on the left side of Power BI Desktop, it’s a The first icon at the top, above the relationship button.
Note: You can add multiple relationships between tables. This enables Power BI developers to have more control over VLOOKUPS and instead of having to concatenate a common key, you can drag and drop multiple columns on each other and Power BI will show matches based on multiple criteria.
The resulting Table will look like the following:
The Cookie Name column is now rendered in the visual even though it is on a separate table from the rest of the data.
Re-creating a VLOOKUP in Power BI by Merging Tables
Merging tables recreates the effect of a VLOOKUP by combining two separate datasets based on the values in each row. It can be a better option than relationships if you prefer working in a single table, or want to limit the number of columns that you bring in from the second data set.
To merge tables together, first launch the Power Query Editor
The fastest way to launch the Power BI Query Editor is to right click the title of a column in the data panel and click” Edit Query”
On the next screen, click on Home > Merge Queries
When merging queries in Power BI you have the option to merge both data sets together, or merge as new.
- Merge as New – Creates a third table of the two previous tables combined
- Merge – Consolidates two tables into a single table
To combine the tables, all you have to do is click to highlight the common columns between both datasets
To recreate a VLOOKUP you can leave the Join Kind set to Left Outer.
The last step is to expand the newly merged table. If you look at the top of your columns, there will be a two sided arrow next to the name of a column. Click on it, and select the columns you want to include in the newly merged table.
There are actually a number of additional ways that you can join or merge data together in Power BI. This controls how the rows from both tables are presented. Sometimes you want to show only data that matches from one table or the other.
The other join types that are available include:
- Inner Join – Keeps only the matching rows from both tables.
- Left Join – Includes all rows from the left table and matching rows from the right table.
- Right Join – Includes all rows from the right table and matching rows from the left table.
- Full Outer Join – Combines all rows from both tables, including unmatched rows.
- Cross Join – Generates all possible combinations of rows from both tables.
Joins and merges are an incredibly useful feature of Power BI. If you’re interested in learning more about them and what they look like, the following Youtube Video provides additional information.
Re-creating a VLOOKUP in Power BI with LOOKUPVALUE and RELATED
Another option for recreating a VLOOKUP in Power BI is to use the LOOKUPVALUE or RELATED DAX functions. We typically don’t recommend them compared to using a relationship or merging datasets because they can be tricker to use and harder for less technical Power BI developers to make adjustments to.
The LOOKUPVALUE function in DAX is used to lookup the value of a table based on defined criteria. It does not require a relationship to previously be setup. One challenge with the formula is that it will not work if there are multiple instances of the same values in a table.
The RELATED function is similar to the LOOKUPVALUE but requires a relationship to be previously established in the Power BI Data Model.
Can You Use Excel Formulas in Power BI?
You cannot use Excel formulas in Power BI. Power BI uses Data Analysis Expressions (DAX) instead of Excel Formulas. They are different languages. Microsoft is integrating AI features into Power BI and we are optimistic that creating formulas will be easier with Power BI Copilot in the near future.
The following is an example of Power BI converting text to a Power BI DAX Expression. Unfortunately, it did not correctly reflect how to use a LOOKUPVALUE function, but we are highly optimistic that it will improve over time.
Conclusion
Adding a relationship to a Power BI data model is the simplest and fastest way to recreate an Excel VLOOKUP in Power BI. A second option is to merge multiple tables together. This technique works well with a small number of tables and can be preferred to flatten multiple tables into a single table to work with.
While Power BI has a LOOKUPVALUE function and RELATED function to create custom measures it can be difficult to get to work correctly, and requires a workaround when values repeat across multiple rows in a dataset.