SUBSTITUTEWITHINDEX
A
C
- CALCULATE
- CALCULATETABLE
- CALENDAR
- CALENDARAUTO
- CEILING
- CHISQ.DIST
- CHISQ.DIST.RT
- CHISQ.INV
- CHISQ.INV.RT
- CLOSINGBALANCEMONTH
- CLOSINGBALANCEQUARTER
- CLOSINGBALANCEYEAR
- COALESCE
- COLUMNSTATISTICS
- COMBIN
- COMBINA
- COMBINEVALUES
- CONCATENATE
- CONCATENATEX
- CONFIDENCE.NORM
- CONFIDENCE.T
- CONTAINS
- CONTAINSROW
- CONTAINSSTRING
- CONTAINSSTRINGEXACT
- CONVERT
- COS
- COSH
- COT
- COTH
- COUNT
- COUNTA
- COUNTAX
- COUNTBLANK
- COUNTROWS
- COUNTX
- COUPDAYBS
- COUPDAYS
- COUPDAYSNC
- COUPNCD
- COUPNUM
- COUPPCD
- CROSSFILTER
- CROSSJOIN
- CUMIPMT
- CUMPRINC
- CURRENCY
- CURRENTGROUP
- CUSTOMDATA
D
E
I
N
O
P
R
S
- SAMEPERIODLASTYEAR
- SAMPLE
- SEARCH
- SECOND
- SELECTCOLUMNS
- SELECTEDMEASURE
- SELECTEDMEASUREFORMATSTRING
- SELECTEDMEASURENAME
- SELECTEDVALUE
- SIGN
- SIN
- SINH
- SLN
- SQRT
- SQRTPI
- STARTOFMONTH
- STARTOFQUARTER
- STARTOFYEAR
- STDEVX.P
- STDEVX.S
- STDEV.P
- STDEV.S
- SUBSTITUTE
- SUBSTITUTEWITHINDEX
- SUM
- SUMMARIZE
- SUMMARIZECOLUMNS
- SUMX
- SWITCH
- SYD
T
U
One of the useful DAX functions in Power BI is SUBSTITUTEWITHINDEX. This function replaces a specific part of a text string with another text string, based on the position of the characters within the original text string.
In this article, we will discuss how to use the SUBSTITUTEWITHINDEX function in Power BI, and provide examples of how it can be useful in your data analysis.
Syntax of SUBSTITUTEWITHINDEX
Before we dive into the examples, let’s take a look at the syntax of the SUBSTITUTEWITHINDEX function:
SUBSTITUTEWITHINDEX(❰text❱, ❰old_text❱, ❰new_text❱, ❰instance❱)
- `❰text❱`: This is the text string that you want to modify.
- `❰old_text❱`: This is the text that you want to replace.
- `❰new_text❱`: This is the text that you want to replace `❰old_text❱` with.
- `❰instance❱`: This is the instance of `❰old_text❱` that you want to replace. If you omit this parameter, all instances of `❰old_text❱` will be replaced.
Examples of SUBSTITUTEWITHINDEX
Let's take a closer look at some examples of how to use the SUBSTITUTEWITHINDEX function in Power BI.
Example 1: Replace the first instance of a text string
Suppose you have a table with a column called "Product Name", and you want to replace the first instance of the word "Small" with "Large". You can use the SUBSTITUTEWITHINDEX function in the following way:
=SUBSTITUTEWITHINDEX([Product Name], “Small”, “Large”, 1)
This formula will replace the first instance of "Small" with "Large" in the "Product Name" column.
Example 2: Replace the last instance of a text string
Suppose you have a table with a column called "Product Description", and you want to replace the last instance of the word "Shipping" with "Delivery". You can use the SUBSTITUTEWITHINDEX function in the following way:
=SUBSTITUTEWITHINDEX([Product Description], “Shipping”, “Delivery”, COUNTROWS(FILTER(ROW([Product Description]),[Product Description]&”Shipping”=EARLIER([Product Description])&”Shipping”)))
This formula will replace the last instance of "Shipping" with "Delivery" in the "Product Description" column.
Example 3: Replace a specific instance of a text string
Suppose you have a table with a column called "Order ID", and you want to replace the second instance of the word "X" with "Y". You can use the SUBSTITUTEWITHINDEX function in the following way:
=SUBSTITUTEWITHINDEX([Order ID], “X”, “Y”, 2)
This formula will replace the second instance of "X" with "Y" in the "Order ID" column.
The SUBSTITUTEWITHINDEX function is a powerful tool in Power BI that allows you to replace specific instances of a text string with another string. By using this function, you can modify your data to better fit your analysis needs. We hope this article has been helpful in explaining how to use the SUBSTITUTEWITHINDEX function in Power BI.
