The NATURALLEFTOUTERJOIN function performs a natural left outer join of two tables.
NATURALLEFTOUTERJOIN(
leftJoinTable,
rightJoinTable
)
- leftJoinTable: Existing table or expression that returns a table for the left side of the join.
- rightJoinTable: Existing table or expression that returns a table for the right side of the join.
The NATURALLEFTOUTERJOIN function returns the left table after adding the non-common columns of the right table using the values of the common fields in both tables as keys.
The resulting table will have the columns from the left table plus the uncommon columns from the right table.
The order of the rows in the result table is not guaranteed.
Common columns must have the same data type in both tables.
Only columns with the same lineage (from the same source) are combined.
Suppose we start from the following two tables, "Population":
and "Extension":
...containing respectively the population and extension of a certain number of countries.
We can try to perform a left outer join between these two tables with the following expression:
naturalleftouterjoin = NATURALLEFTOUTERJOIN(Extension, Population)
...but we see how it returns an error message due to a bug when working with this type of combination.
A possible solution is to read the tables using the SELECTCOLUMNS function, adding an empty text string to the common fields:
naturalleftouterjoin =
VAR
LeftTable =
SELECTCOLUMNS(
Population,
"Country", Population[Country] & "",
"Population", Population[Population]
)
VAR
RightTable =
SELECTCOLUMNS(
Extension,
"Country", Extension[Country] & "",
"Extension", Extension[Extension]
)
RETURN
NATURALLEFTOUTERJOIN(LeftTable, RightTable)
