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
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.