The SUBSTITUTEWITHINDEX function theoretically returns the result of a semi-join between two tables made using the common fields that have the same type (see description of a semi-join below) in which the common fields of the left table are replaced by a single column that contains an index to the row in the right table with which the "match" was found.
This assumes that, in theory, the result of the SUBSTITUTEWITHINDEX function should consist of all the columns from the left table that are not found in the right table (that is, the uncommon columns) plus an additional one containing the index corresponding to each row.
However, and as will be seen in an example later, the function only returns the field containing the index.
SUBSTITUTEWITHINDEX(
table,
indexColumnName,
indexColumnsTable,
[orderBy_expression, [order][, orderBy_expression, [order]]...]
)
- table: Left table whose columns not common to the right table will be returned together with the index field.
- indexColumnName: Name of the field that will contain the index.
- indexColumnsTable: Right table that will determine which rows from the left table will be included in the result (those with the same values in the common fields).
- orderBy_expression: Expression that will determine the order to apply to the result.
- order: ASC or DESC, order to apply.
The SUBSTITUTEWITHINDEX function returns a table.
A semi-join is a combination between a "left" table and a "right" table in which only the rows from one of the tables -for example from the left one- for which there are one or more rows in the right table with the same values in the fields common to both are returned. This has two implications:
- Rows in the left table are included in the result only if there are any rows in the right table with the same values in the common fields.
- Rows in the left table are included in the result at most once, even if there is more than one row in the right table with the same values in the common fields.
Despite the sort fields available as arguments in the function, according to Microsoft documentation, the function does not guarantee the order of the result.
Suppose we have the following table of products, prices and quantities:
...and the following table of products and their costs:
We see that there are three common products (B, C and D), so the result of the function should return three rows: those of the table on the left (whatever is chosen) for which there is an equivalent row in the other table (according to the common fields: "Producto" in our case).
If we apply the function considering the price table as the left table and the cost table as the right one, taking into account that the only common column is, as mentioned, Producto, the result of the SUBSTITUTEWITHINDEX function should include the fields Precio, Cantidad (columns of the left table not common to the right one) and the index field to be created. Let's check it out:
Tabla = SUBSTITUTEWITHINDEX(
Precio,
"Index",
Coste,
Coste[Producto], ASC)
We see that the function only returns the indexes to the table on the right: indexes 0, 1 and 2 corresponding to the first three rows of said table, rows with a product name that also exists in the left table.
