In general, when we want to work with data distributed between two or more tables, we start from a data model in which the tables are related to each other by means of "relationships" that involve a key field at the ends of the tables. But it is not always necessary for these relationships to exist.
In this scenario we start from a table of countries:
...and from a second table that includes the extension of the same countries:
Note that the lists are not ordered according to the same criteria.
Once the tables are imported into Power BI, we confirm (since the scenario is based on this hypothesis) that there is no relationship between them:
The goal is to add the Extension field of the homonymous table to the Countries table. If there was a relationship between the tables, it could be achieved by calling the RELATED function. But in this case it doesn't exist. Fortunately we have a good alternative: the LOOKUPVALUE function. This function allows us to extract values from a column by searching one or more columns of the same table for a value (or values) that may or may not exist.
We can take advantage of this by adding a new column to the Countries table that extracts the Extension (from the homonymous table) by looking in the Country column (from the same table) for the value of the country from the Countries table. Let's see it in practice:
LOOKUPVALUE(
'Extension'[Extension]; // Value to extract
'Extension'[Country]; // Lookup column
'Countries'[Country] // Column from which to extract the value to look for
)
