How to Add a Column from Another Table in Power BI

Bringing columns from different tables together is a fundamental task in Power BI, but knowing the right way to do it can be tricky. You might need to add a product category to your sales data or pull a customer's name into a support ticket log. This tutorial will walk you through the most effective methods for adding columns from another table in Power BI, explaining exactly when and how to use each approach.

First Things First: Why Relationships Matter

Before you can pull data from one table into another, Power BI needs to understand how those tables are connected. This connection is called a relationship. Think of it as telling Power BI, "Hey, the 'ProductID' column in my Sales table is the same thing as the 'ProductID' column in my Products table."

Most of the time, you'll be working with a one-to-many relationship. In our example:

• One product in the Products table can appear in many sales transactions in the Sales table.
• So, the one-to-many relationship is from Products to Sales.

You can create and manage these relationships in the "Model" view in Power BI Desktop. Simply click and drag the common column from one table (like ProductID from Products) and drop it onto the corresponding column in the other table (like ProductID from Sales). Making sure this is set up correctly is the essential first step for an effective data model.

Method 1: Use DAX to Create a Calculated Column

Using Data Analysis Expressions (DAX) is the most common way to add a column once your data is already loaded into your Power BI model. This method creates a "calculated column," which exists virtually within your model and takes up very little space. It's flexible, powerful, and preserves a clean, normalized data structure, which is Power BI best practice.

When to use this method:

• Your data is already loaded into Power BI.
• You want to maintain a "star schema" with separate dimension and fact tables.
• You need the new column to be usable in other DAX calculations or measures.

Using the RELATED Function

The RELATED function is your best friend when you have a proper one-to-many relationship set up. It "walks" across the established relationship from the "many" side of your data to the "one" side to grab the value you need.

Let's say you have a Sales table (the "many" side) and a Products table (the "one" side). You want to add the ProductName from the Products table to your Sales table for easier reporting.

Here's how to do it step-by-step:

1. Navigate to the Data view by clicking the table icon on the left-hand panel.
2. Select the table you want to add the column to. In this case, select the Sales table.
3. From the "Column tools" tab in the top ribbon, click New column.
4. The formula bar will appear. Type the following DAX formula and press Enter:

Product Name = RELATED(Products[ProductName])

That's it! Power BI uses the existing relationship on ProductID to look up the corresponding product name for each sale in your Sales table and populates a new column named "Product Name" with that information.

The key rule for RELATED: It only works when you are fetching a value from the "one" side of a relationship to the "many" side. You can't use it to go from Products to Sales, as one product could have many sales, and Power BI wouldn’t know which sale to pick.

Using the LOOKUPVALUE Function

What if you don't have a formal relationship between tables, or the relationship is inactive? That’s where LOOKUPVALUE comes in. It works much like Excel's VLOOKUP, searching for a value in one table that corresponds to a known value in your current table.

The syntax for LOOKUPVALUE is:

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

• result_columnName: The column containing the value you want to retrieve (e.g., Products[ProductName]).
• search_columnName: The column to search in the lookup table (e.g., Products[ProductID]).
• search_value: The value to look for, which comes from your current table (e.g., Sales[ProductID]).

To achieve the same result as our RELATED example, you would use this LOOKUPVALUE formula in a new column in the Sales table:

_Product Name (Lookup) = LOOKUPVALUE( Products[ProductName], Products[ProductID], Sales[ProductID] )

A crucial requirement for LOOKUPVALUE: For it to work correctly and avoid errors, the values in the search_columnName (in this case, Products[ProductID]) must be unique. Since a Product ID should be unique for each product, this formula works perfectly. If there were duplicates in the lookup column, LOOKUPVALUE wouldn't know which result to return and would give you an error.

Method 2: Use Merge Queries in Power Query

The second major method happens before your data even gets loaded into the Power BI data model. By using the Power Query editor, you can "merge" or join tables together, physically adding the columns from one table to another. This is part of the data transformation stage.

When to use this method:

• During the initial data shaping and cleaning process.
• When you deliberately want to create a "flattened" or denormalized table to simplify your final data model.
• The logic is simple, and you prefer a user-friendly interface instead of writing DAX code.

Here’s a full walkthrough of merging the Products table into the Sales table.

Step 1: Open the Power Query Editor

In Power BI Desktop, go to the Home tab on the ribbon and click Transform data. This will launch the Power Query Editor in a new window, where all your data transformation magic happens.

Step 2: Select Your Main Table and Start the Merge

In the list of queries on the left, select the table you want to add columns to - in our case, the Sales table. With the Sales query selected, navigate to the Home tab in the Power Query ribbon and click Merge Queries.

Step 3: Configure the Merge Dialog Box

A dialog box will appear, asking you to configure the merge operation.

1. Your Sales table is already selected as the top table. In the dropdown menu below it, select the Products table.
2. Now, you need to tell Power Query which columns to match on. Click the ProductID column header in the Sales table, and then click the ProductID column header in the Products table. You'll see them highlight, and a note at the bottom will confirm the match.
3. For Join Kind, the default "Left Outer" is perfect for this task. It means "keep all rows from the Sales table, and bring in matching rows from the Products table."
4. Click OK.

Step 4: Expand the New Column to Select Your Data

You’ll be returned to the Power Query Editor. At the far right of your Sales table, you’ll see a new column (likely named "Products") with the word Table in each cell. This structured column contains the entire Products table for each matching row.

To get the specific column you need:

1. Click the expand icon (two arrows pointing in opposite directions) in the column header.
2. A dropdown menu will list all columns available in the Products table. By default, all are selected. Deselect the "(Select All Columns)" checkbox.
3. Now, check only the column(s) you want to add, like ProductName.
4. It's good practice to also uncheck the "Use original column name as prefix" box to avoid getting a clunky column name like Products.ProductName.
5. Click OK.

Step 5: Close & Apply

You'll now see a brand new ProductName column added to your Sales query. To save these changes and load them into your Power BI data model, go to the Home tab and click Close & Apply. Your Sales table in Power BI will now physically contain the "Product Name" column.

DAX vs. Power Query: Which One Should I Use?

Both methods get the job done, but choosing the right one can make your reports more efficient and easier to manage.

Choose Power Query (Merge Queries) when...

• You want to simplify your data model from the start. Merging tables can create a single, wider table that's sometimes easier to work with, especially for simpler reports.
• You are doing it as a one-time setup step during the initial import and transformation phase.
• You're more comfortable with a visual, click-based interface than with writing formulas.

Heads up: Merging tables can increase your file size and memory usage because it duplicates data. A ProductName string is repeated for every sale of that product, whereas the DAX approach just stores the reusable link between tables.

Choose DAX (RELATED) when...

• You are following best practices by building a "star schema," where descriptive tables (your "dimensions" like Products) are kept separate from your data tables (your "facts" like Sales). This is the most scalable and efficient approach for most Power BI projects.
• Your data model is already built, and you just think of a new analysis that requires some related data. Creating a calculated column is quick and easy.
• You want a lightweight, efficient model. RELATED doesn't physically add the data everywhere, it just looks it up when needed, keeping your model trim.

Final Thoughts

Adding columns from other tables is a skill you'll use constantly in Power BI. The two primary paths - using DAX functions like RELATED on your loaded data model or using Power Query's Merge Queries during data transformation - each have clear strengths. Learning to pick the right one for the job will help you build cleaner, faster, and more effective reports.

These kinds of data consolidation tasks are exactly what inspired us to build Graphed. We know that marketing, sales, and operations teams often need answers without wanting to get bogged down in data modeling or learning a new formula language. Instead of figuring out joins or DAX, we allow you to connect all your data sources - from Google Analytics to Shopify to Salesforce - and just ask your questions. Telling Graphed to "chart my revenue by product category from Shopify" instantly handles all the data behind the scenes, so you can focus on the insight, not the setup.