NATURALINNERJOIN

Two tables joined on the columns they share; rows without a match left out.

Group: Table manipulation. Compatible with DAX.

Syntax

NATURALINNERJOIN ( <LeftTable>, <RightTable> )

Parameter	Type	Description
`LeftTable`	Table	The first table; its columns come first in the result.
`RightTable`	Table	The second table, joined to the first on the columns they share.

Returns

Table. The pairs of rows that agree on every shared column: the left table's columns, then the right one's others.

Remarks

The shared columns are those of the same name for tables made in the query, and the same column of the model otherwise. VALUES ( Products[Product] ) and VALUES ( Sales[Product] ) share none, though the names agree.
Two tables of the model with no shared column join along the relationship between them.
Shared columns must have the same type: a number column does not join a text column. Without any shared column the call is an error.
Text matches without regard to case, and a blank matches a blank.
Rows of either table without a partner are left out; NATURALLEFTOUTERJOIN keeps those of the left table.
To join on columns with different names, rename one with SELECTCOLUMNS first; an expression there, such as a column & "", also drops its tie to the model.

Examples

Order lines with their prices

Stout has no price and Cider no order: neither appears.

EVALUATE
VAR Orders =
    DATATABLE (
        "Product", STRING, "Liters", INTEGER,
        { { "Pils", 120 }, { "IPA", 80 }, { "Stout", 45 }, { "Pils", 30 } }
    )
VAR PriceList =
    DATATABLE (
        "Product", STRING, "Price", DOUBLE,
        { { "Pils", 38 }, { "IPA", 64 }, { "Cider", 52 } }
    )
RETURN
    NATURALINNERJOIN ( Orders, PriceList )
ORDER BY [Product], [Liters]

Product	Liters	Price
IPA	80	64
Pils	30	38
Pils	120	38

Joined on two columns

EVALUATE
VAR Sold =
    DATATABLE (
        "Shop", STRING, "Product", STRING, "Liters", INTEGER,
        { { "A", "Pils", 120 }, { "B", "Pils", 10 }, { "A", "IPA", 30 } }
    )
VAR Targets =
    DATATABLE (
        "Shop", STRING, "Product", STRING, "Target", INTEGER,
        { { "A", "Pils", 100 }, { "A", "IPA", 50 }, { "B", "IPA", 20 } }
    )
RETURN
    NATURALINNERJOIN ( Sold, Targets )
ORDER BY [Shop], [Product]

Shop	Product	Liters	Target
A	IPA	30	50
A	Pils	120	100

NATURALLEFTOUTERJOIN, CROSSJOIN, GENERATE, SELECTCOLUMNS, INTERSECT.

Last updated 3 November 2026 · OpenBI Studio 0.3.74