Graphed LogoGraphed
Login

How to Join Two Datasets in Power BI Report Builder

Cody Schneider•

Trying to show data from two different datasets in a single table inside Power BI Report Builder can be frustrating. Unlike in Power BI Desktop, you can’t just drag and drop fields from multiple queries and expect them to magically relate. This article will show you exactly how to join two datasets using expressions so you can finally build the unified paginated report you need.

Why Combine Datasets in Report Builder?

Power BI Report Builder is the go-to tool for creating pixel-perfect, printer-friendly paginated reports. However, your data isn't always neatly organized in a single source. You'll often find yourself needing to merge datasets in these common situations:

  • Enriching Transaction Data: Your main dataset might contain sales transactions with just a CustomerID or ProductID. A separate dataset contains the full customer name, address, or product description. You need to combine them to create a human-readable invoice or sales summary.

  • Combining Data from Different Sources: You might have sales data from a SQL database and sales goals from an Excel file or SharePoint list. To show actuals versus goals, you need to bring these two sources together in your report.

  • Reducing Query Complexity: Sometimes, it can be more efficient to run two simple, fast queries and join them in the report itself, rather than writing a single highly complex SQL query that is slow to execute.

The core challenge is that Report Builder treats each dataset as an independent island of information. To bridge them, you can't use a visual relationship model, you have to use built-in functions.

The Solution: The LOOKUP and LOOKUPSET Functions

If you're familiar with VLOOKUP in Excel, you're already halfway there. Power BI Report Builder has two similar, incredibly useful functions for this exact task: LOOKUP and LOOKUPSET. These expression-based functions allow you to "look up" a value in a second dataset based on a matching key from your primary dataset.

Let's walk through exactly how to use them with a practical example.

Step 1: Setting Up Your Sample Datasets

First, you need at least two datasets that share a common column or key. This key is how we'll match records between them. For this tutorial, imagine we have sales data and we want to add product details like the full product name and its category.

In your Report Builder project, create two datasets:

Dataset 1: SalesData

This is our primary dataset. It contains the core transaction information. It should have a field we can use as a key, like ProductID.

Example fields:

  • OrderQTY

  • LineTotal

  • ProductID

Dataset 2: ProductDetails

This is our secondary or "lookup" dataset. It contains the descriptive information we want to pull into our main report. It must also contain the same key field, ProductID.

Example fields:

  • ProductID

  • ProductName

  • ProductSubcategory

Once you’ve set up your data sources and created these two datasets in the Report Data pane, you're ready to create a table and join them.

Step 2: Using the LOOKUP Function (For One-to-One Matches)

The LOOKUP function is perfect when there is a one-to-one relationship between your datasets. This means for every key in your primary data (SalesData), there is only one corresponding record in your lookup data (ProductDetails). In our case, each ProductID corresponds to exactly one product name.

The syntax for the LOOKUP function is:

=LOOKUP(source_expression, destination_expression, result_expression, dataset_name)

Let's break that down:

  • source_expression: The key value from your primary dataset. Example: Fields!ProductID.Value

  • destination_expression: The key value in your lookup dataset that you want to match against. Example: Fields!ProductID.Value

  • result_expression: The field from the lookup dataset that you want to return. Example: Fields!ProductName.Value

  • dataset_name: The name of the lookup dataset, in quotation marks. Example: "ProductDetails"

How to Implement the LOOKUP Function

  1. Create a Table: Insert a table into your report and set its data source to your primary dataset, SalesData. Drag fields from SalesData like ProductID and LineTotal into the columns.

  2. Add a New Column: Right-click the header of the last column and choose Insert Column → Right. Name this new column "Product Name".

  3. Open the Expression Editor: Right-click on the empty data cell below your new "Product Name" header and select Expression....

  4. Write the LOOKUP Expression: In the expression editor, type the following formula. This tells Report Builder to find the ProductID from the current row of the SalesData dataset, look for a match in the ProductID column of the ProductDetails dataset, and return the corresponding ProductName.

=LOOKUP(Fields!ProductID.Value, Fields!ProductID.Value, Fields!ProductName.Value, "ProductDetails")

  1. Click OK and Run: Click OK to close the editor. When you run the report, the "Product Name" column will populate with the correct data from your ProductDetails dataset.

You can repeat this process to add more fields. To add the product subcategory, simply add another column and use this expression:

=LOOKUP(Fields!ProductID.Value, Fields!ProductID.Value, Fields!ProductSubcategory.Value, "ProductDetails")

Step 3: Using the LOOKUPSET Function (For One-to-Many Matches)

What if one key in your primary data corresponds to multiple records in your secondary data? For example, what if you wanted to list all Sales Order numbers associated with a single customer?

This is where LOOKUPSET comes in. It works just like LOOKUP, but instead of returning a single value, it returns an array (a collection) of all matching values.

The syntax is identical:

=LOOKUPSET(source_expression, destination_expression, result_expression, dataset_name)

The problem is, if you just use LOOKUPSET by itself in a table cell, the output will look like "#Error" or "System.Object[]" because Report Builder doesn't know how to display an array. To fix this, you need to combine it with another function: JOIN.

The JOIN function takes an array and turns it into a readable string of text, separated by a delimiter of your choice (like a comma).

How to Implement LOOKUPSET and JOIN

Let's change our goal slightly. Assume we have a primary dataset of ProductCategories and we want to list all of the ProductNames that fall under each category in a single cell.

  1. Set Up the Table: Create a table based on the ProductCategories dataset. Add a column for CategoryName.

  2. Add a "Products" Column: Add a new empty column to the right, and title it "Products in Category".

  3. Write the Combined Expression: Open the expression editor for the data cell in that new column. This time, we'll wrap our LOOKUPSET inside a JOIN function.

=JOIN(LOOKUPSET(Fields!ProductSubcategory.Value, Fields!ProductSubcategory.Value, Fields!ProductName.Value, "ProductDetails"), ", ")

Breaking this down:

  • The LOOKUPSET part finds all the ProductName values in the ProductDetails dataset that match the current ProductSubcategory.

  • It returns an array like ["Product A", "Product B", "Product C"].

  • The JOIN function takes that array and the delimiter ", " and turns it into the clean string: Product A, Product B, Product C.

When you run the report, you'll see a neat, comma-separated list of all products for each category, all condensed into one cell.

Best Practices for Performance

While these functions are powerful, they can slow down your report if not used carefully, as the lookup is performed for every single row.

  • Keep Lookup Datasets Small: In your SQL or source query for the lookup dataset, only select the columns you absolutely need — the key column and the result column(s). Don't use SELECT *. The less data Report Builder has to sift through, the faster it will run.

  • Join in the Source When Possible: The most efficient place to join data is almost always at the source (e.g., in your SQL query with a JOIN clause or in a Power BI dataflow). Use LOOKUP and LOOKUPSET when joining at the source is not practical or when dealing with disparate sources like a database and a SharePoint list.

  • Be Careful with Strings: Dataset names in expressions are literal strings and must be spelled perfectly, including case sensitivity. "ProductDetails" is not the same as "Product Details". A typo here is the most common reason for errors.

Final Thoughts

Power BI Report Builder requires a different approach to data modeling than Power BI Desktop, but joining datasets is entirely possible once you're comfortable with expressions. Using the LOOKUP function for one-to-one matches and LOOKUPSET for one-to-many relationships gives you the flexibility to combine and enrich your data to create powerful paginated reports.

This process of writing formulas and wrangling separate datasets is a very common challenge in business intelligence. We built Graphed to remove this friction entirely. Instead of configuring datasets and writing expressions, you simply connect your data sources (like Google Analytics, Shopify, or your database) and use plain English to ask what you need. A prompt like, "Show me our top 10 products by sales revenue and include their category" would automatically handle the join and generate the report for you, all in real-time.