NATURALLEFTOUTERJOIN
Joins two tables on the columns they share, with every row of the first table included.
Group: Table manipulation. Compatible with DAX.
Syntax
NATURALLEFTOUTERJOIN ( <LeftTable>, <RightTable> )| Parameter | Type | Description |
|---|---|---|
LeftTable | Table | The first table; every one of its rows appears in the result. |
RightTable | Table | The second table, joined to the first on the columns they share. |
Returns
Table. Every row of the left table with the matching rows of the right, or blanks where none matches.
Remarks
- Shared columns are found as in
NATURALINNERJOIN: the same name for tables made in the query, the same column of the model otherwise. They must have the same type. - A left row with several matches appears once for each.
- Right rows without a partner are left out.
- Text matches without regard to case, and a blank matches a blank.
Examples
Every order line, priced where possible
Stout has no price: its row stays, with a blank price.
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
NATURALLEFTOUTERJOIN ( Orders, PriceList )
ORDER BY [Product], [Liters]| Product | Liters | Price |
|---|---|---|
| IPA | 80 | 64 |
| Pils | 30 | 38 |
| Pils | 120 | 38 |
| Stout | 45 | (blank) |
Sales against targets
Shop B has no Pils target. Its IPA target has no sales and stays out.
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
NATURALLEFTOUTERJOIN ( Sold, Targets )
ORDER BY [Shop], [Product]| Shop | Product | Liters | Target |
|---|---|---|---|
| A | IPA | 30 | 50 |
| A | Pils | 120 | 100 |
| B | Pils | 10 | (blank) |