SUBSTITUTE

How to Use the Power BI DAX function SUBSTITUTE

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.

Power BI DAX Training Courses by G Com Solutions (0800 998 9248)

Upcoming Courses

11-13 November 2024

Contact Us

    Subject

    Your Name (required)

    Company/Organisation

    Email (required)

    Telephone

    Training Course(s)

    Your Message

    Upload Example Document(s) (Zip multiple files)