How to Match Two Columns in Power BI

Trying to make sense of your data often means bringing different pieces together, like matching customer information with your sales records. In Power BI, combining data based on a common column is one of the most fundamental skills you'll need to build meaningful reports. This guide will walk you through the most effective ways to match two columns, from the easy-to-use visual editor to a more flexible formula-based approach.

Graphed

Build AI Agents for Marketing

Build virtual employees that run your go to market. Connect your data sources, deploy autonomous agents, and grow your company.

Build Your First Agent

Why Do You Need to Match Columns Anyway?

Before jumping into the "how," it helps to understand the "why." Matching columns is the foundation for almost all insightful data analysis. It allows you to:

• Enrich Your Data: You might have a table with sales transactions that only contains a ProductID. By matching it with a second table of product details, you can pull in richer information like the Product Name, Category, or Price, making your sales reports far more useful.
• Combine Disparate Datasets: Imagine you have marketing campaign data from Facebook Ads in one table and sales data from Shopify in another. You could match them using a campaign identifier to see which ads are actually driving revenue.
• Perform Lookups: Sometimes you just need to check if an item in one list exists in another. For example, you could compare a list of event attendees against your master CRM contact list to see who is a new lead.
• Create a Unified Data Model: Properly linking your tables is what turns a collection of separate spreadsheets into a coherent, interactive data model where you can analyze relationships between different aspects of your business.

In Power BI, there are two primary approaches for this: using Merge Queries in the Power Query Editor or writing DAX formulas for calculated columns. Let’s start with the most common and user-friendly method.

Method 1: Using Merge Queries for Robust Data Blending

The Power Query Editor is Power BI’s built-in tool for cleaning, shaping, and transforming your data before it gets loaded into your report. Its "Merge Queries" feature is essentially a super-powered version of Excel's VLOOKUP, designed to combine tables based on matching columns.

This is the best method when you need to permanently add several columns from one table to another during your data prep phase.

Let's use a practical example: We have two tables.

1. A SalesData table with columns like SaleID, OrderDate, and ProductID.
2. A ProductDetails table with columns like ProductID, ProductName, and Category.

Our goal is to add the ProductName and Category to our main SalesData table. Here’s how to do it step-by-step:

Step 1: Open the Power Query Editor

First, you need to get into the data transformation layer of Power BI. In the main ribbon, on the Home tab, click on Transform data. This will open the Power Query Editor in a new window, showing all the data tables in your model.

AI Agents for Marketing

Crash Course

Graphed

Free PDF · the crash course

AI Agents for Marketing Crash Course

Learn how to deploy AI marketing agents across your go-to-market — the best tools, prompts, and workflows to turn your data into autonomous execution without writing code.

Download the PDF

Step 2: Start the Merge Operation

In the Power Query Editor, select the table you want to add columns to. In our case, this is the SalesData table. With this table selected, go to the Home tab in the ribbon and click Merge Queries.

You’ll see two options: Merge Queries and Merge Queries as New.

• Merge Queries: This adds the new data directly into the table you currently have selected (our SalesData table).
• Merge Queries as New: This creates a completely new, combined table, leaving your original tables untouched.

For our purposes, let’s stick with the default Merge Queries to add the columns to our existing sales data table.

Step 3: Configure the Merge Window

A new window will pop up. This is where you tell Power BI how to match the two tables.

1. The top section will already show a preview of your SalesData table.
2. In the dropdown menu below it, select the second table you want to get data from. In this case, select ProductDetails.
3. Next, you need to tell Power BI which columns contain the matching values. Click on the ProductID column in the SalesData table, and then click on the ProductID column in the ProductDetails table. The columns will become highlighted.

Power BI will give you a little message at the bottom of the window telling you how many of the rows match. This is a great way to quickly check if your keys align as you expect.

Step 4: Select the "Join Kind"

The "Join Kind" dropdown tells Power BI how to combine the rows. For beginners, the two most important types are:

• Left Outer (Default): This is the one you’ll use 90% of the time. It keeps all the rows from your first (top) table and brings in any matching rows it finds in the second (bottom) table. If a ProductID from your sales data doesn’t exist in your product details list, the new columns will just be blank (null) for that row.
• Inner: This option only keeps the rows that have a match in both tables. It’s useful for cleaning data if you only want to analyze sales records for products that you have full details on.

Let's stick with the default Left Outer join and click OK.

Step 5: Expand the New Column to See Your Data

You’ll now be back in the Power Query Editor. You’ll see a new column has been added to the end of your SalesData table, likely named “ProductDetails.” Right now, it just says “Table” in every cell. Don't worry, this is correct.

To get the data, click the Expand icon (it looks like two arrows pointing in opposite directions) in the column header.

A dropdown list of all the columns from the ProductDetails table will appear. Now you can choose which specific columns you want to add to your SalesData table.

• Uncheck the box for ProductID, since you already have that column.
• Select the columns you need, like ProductName and Category.
• It's also a good idea to uncheck the box at the bottom that says “Use original column name as prefix.” If you leave it checked, your new columns will be named awkwardly, like ProductDetails.ProductName. Unchecking it gives you cleaner column names.

Click OK. Voila! You now have the product name and category directly in your sales table.

Step 6: Close & Apply

Finally, click the Close & Apply button in the top-left corner of the Power Query Editor. This saves your changes and loads the newly combined table into your Power BI report model.

Graphed

Build AI Agents for Marketing

Build virtual employees that run your go to market. Connect your data sources, deploy autonomous agents, and grow your company.

Build Your First Agent

Method 2: Using DAX for Dynamic Lookups

Sometimes, merging entire tables feels like overkill. What if you just need to look up a single piece of information? This is where DAX (Data Analysis Expressions), Power BI's formula language, comes in handy.

Instead of transforming the data tables themselves, DAX lets you create new calculated columns that pull in values from other tables dynamically. The two most useful functions for this are RELATED() and LOOKUPVALUE().

When to Use RELATED(): The Easy, Relationship-Based Option

The RELATED() function is the simplest way to fetch a value from another table, but it has one requirement: you must have a pre-existing relationship between your tables in the Power BI Model view.

Creating a Relationship

1. Exit the Power Query Editor and go to the Model view in Power BI (the third icon on the left-hand sidebar that looks like a table diagram).
2. Find your SalesData and ProductDetails tables.
3. Click and drag the matching column, ProductID, from one table and drop it directly onto the ProductID column in the other table.

A line will appear connecting the two tables, which signifies they now have a relationship. Power BI now understands how a Product ID in one table relates to a Product ID in the other.

Writing the DAX Formula

Now, go to the Data view (the second sidebar icon). Select your SalesData table. On the Table tools ribbon, click New column. In the formula bar that appears, type the following:

Category = RELATED(ProductDetails[Category])

That's it! Because the tables are related, RELATED() can walk across the relationship from the sales table to the product table and grab the category for each corresponding product. It's clean, efficient, and very easy to read.

AI Agents for Marketing

Crash Course

Graphed

Free PDF · the crash course

AI Agents for Marketing Crash Course

Learn how to deploy AI marketing agents across your go-to-market — the best tools, prompts, and workflows to turn your data into autonomous execution without writing code.

Download the PDF

When to Use LOOKUPVALUE(): The powerful, VLOOKUP-style lookup

What if you don’t have a formal relationship set up between tables, or your lookup logic is a bit more complex? LOOKUPVALUE() is your go-to function. It works much more like Excel's VLOOKUP, searching a column for a specific value and returning a corresponding result from another column.

The syntax looks like this:

LOOKUPVALUE( <result_columnName>, <search_columnName>, <search_value> )

1. result_columnName: The column containing the value you want to return (e.g., ProductDetails[Category]).
2. search_columnName: The column in the "lookup table" that you want to search through (e.g., ProductDetails[ProductID]).
3. search_value: The value you are looking for, which typically comes from a column in your current table (e.g., SalesData[ProductID]).

To use LOOKUPVALUE(), you would create a new calculated column in the SalesData table just as before, but with this formula:

Category (Lookup) = LOOKUPVALUE( ProductDetails[Category], ProductDetails[ProductID], SalesData[ProductID] )

This formula tells Power BI: "For each row in our SalesData table, take the Product ID, go over to the Product Details table, find that same ID in its Product ID column, and return the value from the Category column in that same row."

Merge vs. DAX: Which One Should You Choose?

So, should you use Merge Queries or DAX? Here’s a quick guide to help you decide:

Use Merge Queries in Power Query when:

• You are still in the data cleaning and preparation stage.
• You need to bring over multiple columns from the other table.
• The join is a one-time structural change you want to make to your dataset before analysis.
• You need to perform more complex joins (e.g., an Inner Join) to filter down rows.

Use a DAX Calculated Column (RELATED or LOOKUPVALUE) when:

• Your data is already cleaned and loaded into the model.
• You only need to pull in one specific value.
• You want a more dynamic calculation that doesn’t permanently alter your table structure.
• For RELATED(), you already have (or can easily create) a relationship between the tables.

Final thoughts

Mastering how to match columns is a foundational skill that unlocks the true analytical power of Power BI. For major data shaping, the Merge Queries feature in Power Query offers a visual and robust way to combine tables. For more targeted, on-the-fly lookups inside your data model, DAX functions like RELATED and LOOKUPVALUE provide powerful flexibility. Knowing when to use each approach will help you build cleaner and more efficient reports every time.

Of course, becoming proficient with Power Query and DAX takes time and practice. If you're tired of tutorials and just want answers from your data, you might appreciate the approach we've taken with Graphed. We connect directly to your marketing and sales platforms (like Google Analytics, Shopify, and Salesforce), automating the entire data blending and modeling process. Instead of merging queries or writing formulas, you simply ask questions in plain English, like "Compare Facebook Ads spend vs. revenue by campaign," and Graphed instantly builds you a shareable, real-time dashboard.