How to Do Lookup in Power BI

Struggling to connect related data from different tables in Power BI? It's a common hurdle, a bit like having a sales list filled with Product IDs but needing the actual product names for your report. You need a way to "look up" that information and pull it into your main table. This guide will walk you through the three most effective ways to perform lookups in Power BI, from the simple visual approach to more powerful formula-based methods.

Graphed

Still Building Reports Manually?

Watch how growth teams are getting answers in seconds — not days.

Watch the Demo

Why Lookups are Essential in Power BI

Modern data is rarely stored in one giant, perfect table. It's usually spread across multiple tables to keep things organized and efficient. This is known as a relational data model. For instance, you might have:

• A Sales Table with columns like Date, OrderID, CustomerID, ProductID, and QuantitySold.
• A Products Table with columns like ProductID, ProductName, Category, and Price.
• A Customers Table with columns like CustomerID, CustomerName, and Region.

Your sales table is great for tracking transactions, but it only contains IDs. If your boss asks for a report showing "Total Sales by Product Category," you have a problem. The 'Category' information lives in the Products table, not the Sales table. This is where a lookup comes in. You need to use the common ProductID column to pull the Category from the Products table into your Sales analysis. Doing this enriches your primary data table, unlocking deeper and more meaningful analysis.

The Easiest Method: Using MERGE in Power Query

If you're coming from an Excel background, the Merge Queries feature in Power Query will feel very familiar - it’s Power BI’s robust version of a VLOOKUP. This method is a visual, step-by-step process that joins tables before your data even loads into the Power BI model. It’s perfect for permanently adding columns you’ll need for your analysis.

Let's walk through an example. Imagine you want to add the ProductName from your 'Products' table to your 'Sales' table.

Graphed

Still Building Reports Manually?

Watch how growth teams are getting answers in seconds — not days.

Watch the Demo

Step 1: Open the Power Query Editor

From the main Power BI Desktop window, go to the Home tab and click on Transform data. This will launch the Power Query Editor, which is the backstage area where you clean and prepare all your data.

Step 2: Start the Merge Query Operation

In the Power Query Editor, you'll see a list of your queries (tables) on the left side. Select the table you want to add data to - in our case, the 'Sales' table. With the 'Sales' query selected, go to the Home tab on the ribbon and click Merge Queries.

Quick Tip: If you select a table and choose 'Merge Queries', it will add the new data as a column to your existing query. If you choose 'Merge Queries as New', it will create an entirely new, combined table.

Step 3: Configure the Merge Dialog Box

A new window will appear. At the top, you'll see a preview of your active table ('Sales').

1. Click the dropdown menu below the 'Sales' preview and select the table you want to get data from. In this case, choose the 'Products' table.
2. Now, you need to tell Power Query how these tables relate to each other. Click on the common column header in each table to select them. So, click on ProductID in the 'Sales' table preview, and then click on ProductID in the 'Products' table preview. They should both highlight, and Power Query will show you how many matches it found.
3. For the Join Kind, select Left Outer (all from first, matching from second). This is the most common join type for lookups. It means "keep every row from my 'Sales' table, and wherever there's a matching ProductID in the 'Products' table, bring in that data."
4. Click OK.

Step 4: Expand the New Column

You’ll be taken back to the Power Query Editor, where you'll see a new column at the end of your 'Sales' table, likely named 'Products'. In the column header, you'll see an icon with two arrows pointing in opposite directions. Click this icon.

A dropdown will appear, listing all the available columns from the 'Products' table. Uncheck every box except for the one you want to bring in - in our case, ProductName. It's also a good practice to uncheck "Use original column name as prefix." Then, click OK.

Voilà! A ProductName column now appears in your 'Sales' table. All you need to do is click Close & Apply in the top-left corner to load your newly shaped data into your model.

Graphed

Still Building Reports Manually?

Watch how growth teams are getting answers in seconds — not days.

Watch the Demo

For Related Tables: The DAX RELATED Function

DAX (Data Analysis Expressions) is the formula language of Power BI. If you've already established a relationship between your tables in the Model view, you can use the simple but powerful RELATED function to perform lookups.

The key requirement for RELATED is that your tables must have an active many-to-one relationship. In our example, the 'Sales' table is the "many" side (many sales can have the same product), and the 'Products' table is the "one" side (each product appears only once). The RELATED function works from the "many" side to pull data from the "one" side.

How to Use the RELATED Function

1. Navigate to the Data View in Power BI Desktop (the second icon on the left-hand panel).
2. Select the table you want to add the column to (our 'Sales' table).
3. From the Table tools ribbon that appears, click New column.
4. A formula bar will appear at the top. Here, you'll write your DAX formula. The syntax is very straightforward:

New Column Name = RELATED(<TableName>[<ColumnName>])

To get the product name, you would type:

Product Name = RELATED(Products[ProductName])

Hit Enter, and Power BI will instantly create a new calculated column in your 'Sales' table, populated with the product names. Because it relies on the pre-built relationship, it’s extremely efficient.

The Flexible Powerhouse: The DAX LOOKUPVALUE Function

What if you don't have a formal relationship set up between tables? Or what if you need to match based on multiple criteria? This is where LOOKUPVALUE shines. It’s the closest DAX equivalent to Excel's INDEX/MATCH or VLOOKUP because it doesn't require a pre-defined relationship.

The syntax is a bit more involved, but it gives you incredible flexibility.

LOOKUPVALUE(
    <result_columnName>,
    <search_columnName1>, <search_value1>,
    <search_columnName2>, <search_value2>,
    [Optional: <alternateResult>]
)

• <result_columnName>: The column that contains the value you want to return (e.g., Products[ProductName]).
• <search_columnNameX>: The column in the lookup table you want to search through (e.g., Products[ProductID]).
• <search_valueX>: The value you are looking for in the search column. This is usually the matching column in your current table (e.g., Sales[ProductID]).
• <alternateResult>: What to show if no match is found. This is a great practice to avoid errors.

Graphed

Still Building Reports Manually?

Watch how growth teams are getting answers in seconds — not days.

Watch the Demo

How to Use LOOKUPVALUE

Just like with RELATED, you'll create a New Column in the Data view. Let's replicate our previous example.

Product Name Lookup = 
LOOKUPVALUE(
    Products[ProductName],
    Products[ProductID],
    Sales[ProductID],
    "Not Found"
)

This formula tells Power BI: "Go look in the Products table for the row where Products[ProductID] matches the Sales[ProductID] for the current row. Once you find it, give me the value from the ProductName column. If you can't find a match, just return the text 'Not Found'."

You can even use LOOKUPVALUE with multiple criteria by adding more pairs of search_columnName and search_value arguments.

MERGE vs. DAX Functions: Which Should You Choose?

So, you have three great options. Which one is best? The answer depends on your goal.

Choose Merge Queries in Power Query when...

• You are in the data preparation and cleansing stage.
• You want to physically add a new column to your table before it's loaded. This can sometimes make downstream calculations faster.
• You are comfortable with a visual, click-based interface and the concept of table joins.

Choose RELATED when...

• You already have a clean data model with active relationships between your tables.
• You need an efficient, high-performance solution that leverages your model's structure.
• You are creating a calculated column on the "many" side of a relationship.

Choose LOOKUPVALUE when...

• There is no relationship between the tables, and you don't want to create one.
• You need to match based on multiple criteria.
• You are working within the DAX environment and need a flexible, formula-based solution.

A good rule of thumb: use Power Query (Merge) to shape and build your tables correctly first. Use DAX (RELATED or LOOKUPVALUE) for more dynamic calculations or when modifying the source query isn't an option.

Final Thoughts

Successfully pulling in related data from different tables is a fundamental skill in Power BI. Whether you choose the user-friendly Merge Queries in Power Query, the highly efficient RELATED function, or the flexible LOOKUPVALUE powerhouse, understanding these techniques will dramatically enhance your reports and analysis.

Mastering tools like Power BI is a rewarding journey, but we know the learning curve can feel steep when all you want are quick, clear answers from your business data. That’s why we created Graphed. We turn hours of data analysis into a simple chat. You just connect your sources and then ask questions in plain English - like "show my total revenue by product for last month" - to instantly build the exact dashboard you need, without ever wrestling with table joins or DAX formulas.