The NATURALINNERJOIN function performs a natural inner join of two tables.
NATURALINNERJOIN(
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 NATURALINNERJOIN function returns a table with rows made up of the rows from the two tables involved for which the values in the common columns exist in both tables.
The tables are combined according to the names of the common fields. If the two tables do not have common fields, the function returns an error.
The table returned as the result will have the common columns from the left table and the rest of the columns from the two tables.
The order of the rows in the result table is not guaranteed.
Columns with the same names must also have the same data type.
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 an inner join between these two tables with the following expression:
naturalinnerjoin = NATURALINNERJOIN(Population, Extension)
...but we can see how it returns an error message due to a bug when working with this type of combination. There are two solutions to this:
The first consists of, provided that there is an established relationship between the two Country fields, renaming one of them:
naturalinnerjoin = NATURALINNERJOIN(Population, Extension)
...although, in this case, the function returns the two fields that make up the link.
The second consists of reading the tables using the SELECTCOLUMNS function, adding an empty text string to the common fields:
naturalinnerjoin =
VAR
LeftTable =
SELECTCOLUMNS(
Population,
"Country", Population[Country] & "",
"Population", Population[Population]
)
VAR
RightTable =
SELECTCOLUMNS(
Extension,
"Country", Extension[Country] & "",
"Extension", Extension[Extension]
)
RETURN
NATURALINNERJOIN(LeftTable, RightTable)
