REPLACE

How to Use the Power BI DAX function REPLACE

In this article, we’ll explore what the REPLACE function does, how to use it, and some examples of how it can be applied in real-world scenarios.

What Is the REPLACE Function?

The REPLACE function is a DAX function that allows you to replace a specific portion of a text string with another text string. The syntax of the function is as follows:

REPLACE(❰text❱, ❰start❱, ❰count❱, ❰newtext❱)

- `❰text❱`: The text string that you want to modify.

- `❰start❱`: The position in the string where you want to start the replacement. The first character is at position 1.

- `❰count❱`: The number of characters that you want to replace.

- `❰newtext❱`: The text string that will replace the characters you've specified.

How to Use the REPLACE Function

To use the REPLACE function, you need to follow these steps:

1. Open Power BI Desktop and select a new report or an existing report.

2. Add a new measure or modify an existing measure.

3. Enter the following formula for the measure:

MyMeasure = REPLACE(❰text❱, ❰start❱, ❰count❱, ❰newtext❱)

4. Replace `❰text❱`, `❰start❱`, `❰count❱`, and `❰newtext❱` with the appropriate values for your scenario.

5. Save the measure and use it in your report.

Examples of Using the REPLACE Function

Now that we've covered the basics of the REPLACE function, let's look at some examples of how it can be used in real-world scenarios.

Example 1: Clean Up Phone Numbers

Suppose you have a dataset that contains phone numbers in various formats, such as "(123) 456-7890", "123.456.7890", or "123-456-7890". You want to standardize all phone numbers to the format "1234567890".

Here's how you can use the REPLACE function to achieve this:

PhoneNumber = REPLACE(REPLACE(REPLACE(❰Phone❱, “(“, “”), “)”, “”), “.”, “”)

This formula removes the parentheses and periods from the original phone number, leaving only the digits.

Example 2: Extract Part of a URL

Suppose you have a dataset that contains URLs for various websites, such as "http://www.example.com/page1" or "https://www.example.com/page2". You want to extract only the domain name from each URL.

Here's how you can use the REPLACE function to achieve this:

DomainName = REPLACE(REPLACE(REPLACE(❰URL❱, “http://”, “”), “https://”, “”), “/.*”, “”)

This formula removes the "http://" or "https://" from the original URL, as well as everything after the first forward slash, leaving only the domain name.

Example 3: Replace Null Values

Suppose you have a dataset that contains null values in a column. You want to replace these null values with a default value, such as "N/A".

Here's how you can use the REPLACE function to achieve this:

MyColumn = IF(ISBLANK(❰Column❱), “N/A”, ❰Column❱)

This formula first checks if the value in the column is blank. If it is, it replaces it with "N/A". If it's not, it leaves the original value unchanged.

The REPLACE function is a powerful tool for manipulating text strings in Power BI. By using this function, you can standardize data, extract information from URLs, and replace null values with default values. With the examples provided in this article, you should be able to start using the REPLACE function in your own reports and dashboards.

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

Upcoming Courses

Contact Us

    Subject

    Your Name (required)

    Company/Organisation

    Email (required)

    Telephone

    Training Course(s)

    Your Message

    Upload Example Document(s) (Zip multiple files)