SUBSTITUTEWITHINDEX
A table's rows with the columns it shares with an index table replaced by the matching row's position in that table, in an order.
Group: Table manipulation. Compatible with DAX.
Syntax
SUBSTITUTEWITHINDEX ( <table>, <indexColumnName>, <indexColumnsTable>,
<orderBy_expression> [, <order>] [, <orderBy_expression> [, <order>]] … )| Parameter | Type | Description |
|---|---|---|
table | Table | The rows to rewrite. |
indexColumnName | Text | What to call the column of positions, as a text. |
indexColumnsTable | Table | The table that gives each combination its position. Its columns in common with the first table are the ones replaced. |
orderBy_expression | Expression repeatable, evaluated for each row | A value worked out for each row of the index table that sets its order. |
order | Keyword optional, repeatable | ASC or DESC (when left out), for the order value before it; 1 or TRUE, 0 or FALSE say the same. |
Returns
Table. Each row of the table that finds a match in the index table, with the shared columns swapped for one whole-number column: the match's position, counting from 0.
Remarks
- The shared columns are found as in
NATURALINNERJOIN: the same name for tables made in the query, the same column of the model otherwise. - A row without a match in the index table is left out.
- The positions follow the index table in the order given. Without an order keyword the order is DESC, the largest first; write ASC or DESC after every order value to make the numbering plain to read.
- Power BI's own queries use it to number the columns of a matrix; in a report it is rarely needed.
Examples
Products replaced by their position
Sorted by name from A, Cider is 0, IPA 1 and Pils 2.
EVALUATE
VAR Sold =
DATATABLE (
"Shop", STRING, "Product", STRING, "Liters", INTEGER,
{ { "A", "Pils", 120 }, { "A", "IPA", 80 }, { "B", "Pils", 30 }, { "B", "Cider", 60 } }
)
VAR Beers = DATATABLE ( "Product", STRING, { { "Pils" }, { "IPA" }, { "Cider" } } )
RETURN
SUBSTITUTEWITHINDEX ( Sold, "Product index", Beers, [Product], ASC )
ORDER BY [Shop], [Product index]| Shop | Liters | Product index |
|---|---|---|
| A | 80 | 1 |
| A | 120 | 2 |
| B | 60 | 0 |
| B | 30 | 2 |
A row without a match
Stout is not in the index table, so its row is left out.
EVALUATE
VAR Sold =
DATATABLE (
"Shop", STRING, "Product", STRING, "Liters", INTEGER,
{ { "A", "Pils", 120 }, { "A", "IPA", 80 }, { "B", "Pils", 30 }, { "B", "Stout", 60 } }
)
VAR Beers = DATATABLE ( "Product", STRING, { { "Pils" }, { "IPA" }, { "Cider" } } )
RETURN
SUBSTITUTEWITHINDEX ( Sold, "Product index", Beers, [Product], DESC )
ORDER BY [Shop], [Product index]| Shop | Liters | Product index |
|---|---|---|
| A | 120 | 0 |
| A | 80 | 1 |
| B | 30 | 0 |
Two columns replaced by one
EVALUATE
VAR Sold =
DATATABLE (
"Shop", STRING, "Product", STRING, "Liters", INTEGER,
{ { "A", "Pils", 120 }, { "A", "IPA", 80 }, { "B", "Pils", 30 } }
)
VAR Keys =
DATATABLE (
"Shop", STRING, "Product", STRING,
{ { "A", "Pils" }, { "A", "IPA" }, { "B", "Pils" } }
)
RETURN
SUBSTITUTEWITHINDEX ( Sold, "Key", Keys, [Shop], ASC, [Product], DESC )
ORDER BY [Key]| Liters | Key |
|---|---|
| 120 | 0 |
| 80 | 1 |
| 30 | 2 |