Power BI XLOOKUP: DAX and Power Query Lookup Options

Power BI does not have Excel’s XLOOKUP function in DAX. To match a value from another table, use a model relationship with RELATED when possible, LOOKUPVALUE for a DAX lookup without a suitable relationship, or Merge Queries in Power Query when you want to combine data during refresh.

What “XLOOKUP in Power BI” means

XLOOKUP is an Excel worksheet function: it searches one range for a value and returns a corresponding value from another range. Power BI uses a different calculation language and data model. DAX, the language for calculated columns and measures, does not provide a function named XLOOKUP.

People usually ask about XLOOKUP when they have two tables—for example, sales transactions and a customer list—and want to bring a customer name, category, or region into the sales data. Power BI can do this, but its preferred approach is often not a row-by-row formula. A relationship between the tables lets the model connect matching keys and filter results across visuals.

The right substitute depends on when the match should happen and how the result will be used. A relationship supports interactive analysis; a DAX calculated column stores a result in the model; and a Power Query merge joins data as part of the import or refresh process.

Why Power BI often uses relationships instead

A Power BI model is designed to hold related tables, rather than flatten every lookup into one large table. For example, a Sales table can contain transaction rows and a Customers table can contain one row per customer. A relationship connects their shared CustomerID fields. A visual can then group sales by customer name without copying that name into every Sales row.

For this common design, Customers is the “one” side and Sales is the “many” side. The customer key should be unique in Customers, while the same key can appear on many Sales rows. The relationship lets filters from Customers affect Sales, so a slicer for a customer region can filter sales totals.

Use a relationship when the tables represent distinct entities and you want the model to analyze them together.
Use a calculated column when you specifically need a value stored on each row, such as a category used for row-level logic.
Use Power Query when the tables should be physically combined during data preparation or when shaping the data before it enters the model.

Keeping separate tables can reduce repeated data and make the model easier to maintain. It also avoids creating a new lookup expression for every field that needs to be analyzed.

Use RELATED when a relationship already exists

RELATED is usually the closest DAX option to a simple lookup when a many-to-one relationship is in place. It retrieves a value from the “one” side of that relationship for the current row. It is commonly used in a calculated column on the many-side table; it is not a replacement for a relationship itself.

For example, assume Customers has one row per CustomerID, with a Segment column, and Sales has CustomerID on each transaction row. A one-to-many relationship runs from Customers[CustomerID] to Sales[CustomerID]. In a calculated column in Sales, this DAX expression returns each transaction’s customer segment:

Sales[Customer Segment] = RELATED(Customers[Segment])

If the customer on a Sales row belongs to the “Business” segment, that row’s new column returns Business. Because the value is stored for every transaction, it can increase model size when the fact table is large. If the purpose is only to group or filter report results, using the relationship directly in visuals is generally more efficient than adding a repeated column.

RELATED needs row context, which a calculated column naturally has. It also needs a valid relationship that lets DAX find one related value. If no appropriate relationship exists, or the relationship direction and table structure do not support the lookup, it may not work as expected.

Use LOOKUPVALUE when you need a DAX lookup

LOOKUPVALUE searches a specified column for matching key values and returns a value from another column in the searched table. It can be useful when you need lookup behavior but cannot use RELATED, though a proper relationship is usually preferable for a reusable model.

Assume Customers contains CustomerID and Segment, while Sales contains CustomerID. If the Sales table does not have a usable relationship to Customers, this calculated-column expression looks up the segment for each Sales row:

Sales[Customer Segment] = LOOKUPVALUE(Customers[Segment], Customers[CustomerID], Sales[CustomerID])

The expression asks DAX to find a Customers row whose CustomerID equals the current Sales row’s CustomerID, then return that row’s Segment. For instance, if CustomerID 104 has Segment Retail, a Sales row with CustomerID 104 returns Retail. If no matching key exists, LOOKUPVALUE returns blank unless an alternate result is supplied.

Check key uniqueness and data quality before relying on the result. If multiple matching rows have different values in the result column, LOOKUPVALUE cannot choose one and returns an error unless an alternate result is provided. Multiple matches with the same result value can return that shared value, but duplicate keys often indicate a modeling problem worth resolving. Also make sure the key columns have compatible data types and consistent formatting; a number stored as text will not necessarily match the same digits stored as a number.

Merge tables in Power Query

Power Query is a good choice when you want to add lookup fields while preparing data, before it is loaded into the model. In Power BI Desktop, open Power Query Editor and choose Home > Merge Queries, or Home > Merge Queries as New if you want to preserve the original queries and create a separate result.

Select the first table and the matching table in the merge dialog.
Select the key column in each table. If the key contains multiple fields, select the corresponding columns in the same order.
Choose a join kind. Left outer keeps every row from the first table and adds matching values from the second; other join kinds retain different sets of matches.
After the merge, expand the resulting table column and select the fields to bring into the output.

For a sales-to-customer lookup, a left outer merge keeps all sales rows, including sales with no matching customer, and adds customer fields where a match exists. Those unmatched fields appear as null in Power Query. A merge changes the query output and is applied again when data refreshes; it is not a formula that recalculates interactively as a report user changes a slicer.

Before merging, check that the intended lookup key is unique in the customer table. Duplicate matches can cause the merge to produce more than one output row for a single sales row, which may inflate totals. Also confirm the columns have compatible data types and trim or standardize inconsistent text keys when appropriate.

Choose the right approach for a concrete case

Suppose Sales has 500,000 transactions, while Customers has 8,000 unique customers. You want a report of sales by customer segment. If the tables have a valid relationship, the simplest approach is to use Customers[Segment] with a sales measure in a visual. No XLOOKUP-style column is needed: the relationship supplies the connection.

If a downstream calculation specifically needs Segment on every Sales row, RELATED can create a calculated column. If the tables cannot be related in the model but the lookup must be expressed in DAX, LOOKUPVALUE may suit the requirement. If the combined fields should be prepared during refresh, merge the tables in Power Query. The best choice is not determined just by which formula looks most familiar; it depends on the model and the intended result.

Before choosing, ask:

Will report users need the tables to filter each other dynamically? Prefer a relationship.
Does each row need a stored, row-level attribute? Consider RELATED or, in a suitable case, LOOKUPVALUE.
Should the data be joined before it reaches the model? Use a Power Query merge.
Are lookup keys unique and consistent? Fixing duplicates and type mismatches may be more important than selecting a different function.

Common problems and misconceptions

A frequent misconception is that Power BI should accept an Excel formula simply because both products handle tabular data. DAX is not Excel formula syntax, and an Excel XLOOKUP expression cannot be pasted into a DAX calculated column or measure. If XLOOKUP is already used in an Excel source file, Power BI may import the resulting source data, but that does not make XLOOKUP available inside the Power BI model.

Another common issue is duplicate lookup keys. A relationship’s “one” side must have unique values for the intended one-to-many design. If the supposed customer table has several rows for one CustomerID, first determine whether the table should be deduplicated, corrected, or modeled with a different structure. Simply replacing RELATED with LOOKUPVALUE does not automatically fix ambiguous data.

Blank results also need investigation. They can mean the key genuinely has no match, but they can also point to inconsistent spaces, different data types, missing values, or an incorrect key selection. Compare a few known records in both tables and check the actual stored values rather than only how they are displayed.

Finally, avoid adding a calculated column just to make a field available in a visual. Relationships can expose related dimensions without repeating their text on every fact row. Calculated columns are stored in the model, while measures are evaluated in the context of a report query; they serve different purposes and are not interchangeable lookup mechanisms.

Frequently Asked Questions

Can I use XLOOKUP directly in Power BI?
No. XLOOKUP is an Excel function, not a DAX function. Use a relationship, RELATED, LOOKUPVALUE, or a Power Query merge, depending on what the model needs.

Is RELATED better than LOOKUPVALUE?
When a suitable relationship exists, RELATED is generally the clearer choice for retrieving a related value in row context. LOOKUPVALUE is useful for a specific key-based lookup, but it does not replace good relationship design.

How do I return a value when there is no match?
RELATED and LOOKUPVALUE can return blank when no matching value is available. LOOKUPVALUE also supports an optional alternate result. In Power Query, an unmatched left-outer merge produces nulls in the expanded fields.

Does a Power Query merge update when a slicer changes?
No. A merge is part of query preparation and is applied when the data refreshes. Relationships, by contrast, let filters and slicers affect how related tables are used in report analysis.

Why does LOOKUPVALUE return an error?
A common cause is that the search finds multiple rows with different values in the requested result column. Check whether the key should be unique, whether the wrong search column was selected, and whether the source data needs cleanup.

Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.

More in Formulas & functions

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.