SUBSTITUTE
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
Syntax of the SUBSTITUTE Function
The syntax of the SUBSTITUTE function in Power BI is as follows:
SUBSTITUTE(text, old_text, new_text, [instance_num])
In this function:
- `text` is the text string that you want to modify.
- `old_text` is the text that you want to replace.
- `new_text` is the text that you want to replace `old_text` with.
- `[instance_num]` is an optional argument that specifies which occurrence of `old_text` you want to replace. If you don't specify this argument, all occurrences of `old_text` will be replaced.
How to Use the SUBSTITUTE Function
To use the SUBSTITUTE function, you need to follow these steps:
1. Open the Power BI Desktop.
2. Click on the "New Measure" button in the "Modeling" tab.
3. Type in a name for the new measure.
4. In the formula bar, enter the syntax for the SUBSTITUTE function, replacing the placeholders with the appropriate values.
For example, if you want to replace the word "blue" with "red" in the text string "The sky is blue", you would enter the following formula:
New Text = SUBSTITUTE(“The sky is blue”, “blue”, “red”)
Examples of Using the SUBSTITUTE Function
Here are some examples of how to use the SUBSTITUTE function in Power BI:
Example 1: Replace All Occurrences of a Value
Suppose you have a table with a column called "Product Name", and you want to replace all occurrences of the word "widgets" with "gadgets". You can use the SUBSTITUTE function like this:
New Product Name = SUBSTITUTE([Product Name], “widgets”, “gadgets”)
Example 2: Replace a Specific Occurrence of a Value
Suppose you have a table with a column called "Product Name", and you want to replace the second occurrence of the word "widgets" with "gadgets". You can use the SUBSTITUTE function like this:
New Product Name = SUBSTITUTE([Product Name], “widgets”, “gadgets”, 2)
Example 3: Replace a Value Based on a Condition
Suppose you have a table with a column called "Product Name", and you want to replace all occurrences of the word "widgets" with "gadgets" only if the product is a widget. You can use the SUBSTITUTE function like this:
New Product Name = IF([Product] = “Widget”, SUBSTITUTE([Product Name], “widgets”, “gadgets”), [Product Name])
The SUBSTITUTE function in Power BI is a powerful tool for manipulating text strings. By using this function, you can easily replace specific values in a text string with other values, based on certain conditions. Hopefully, this article has helped you understand the syntax of the SUBSTITUTE function and how to use it in your own Power BI projects.
