Power Pivot Principles: Introducing the REPLACE Function

Welcome back to our Power Pivot blog.  Today, we look at the REPLACE function.

The REPLACE function is used in creating custom columns in the ‘Power Pivot for Excel’ window, to create new groupings or categories for datasets.  The REPLACE function replaces part of a text string in each row in a column, based on the number of characters specified by the user, with a different text string.

The REPLACE function uses the following syntax to operate:

• the <old_text> parameter is the string of text that contains the characters that we want to replace, this can also be a reference to a column that contains text
• the <start_num> parameter is the position of the first character in the <old_text> that we wish to replace with <new_text>
• the <num_chars> parameter is the number of characters that we want to replace
• the <new_text> parameter is the text that is going to replace the <old_text>.

Let’s move on to an example.  This week, we are going to use the following extremely complicated dataset:

Picture that an intern has accidentally typed in “AB” in front of all the product names, instead of “New”.  Sure, we can fix this in Excel, but let’s do it in DAX.  We can use the REPLACE function in DAX to create a calculated column that will correct this step.

After adding the data table to our data set in Power Pivot, we can create a calculated column with the following DAX function:

As a side note, we have entered ‘1’ as the <start_num> as “AB” is in the first character position in the “AB Gloves” text string.  If we wanted to replace “Gloves” we would specify ‘4’ as the <start_num>: this is because we count each letter and blank in the text string as a character position. 

Problem solved! The REPLACE function works well with replacing text strings “AB” that all begin in the same spot in the text field. What if the AB was at the end of the text string?

We can specify the <start_num> as ‘8’ since “AB” begins on the eighth character position in “Gloves AB”.

However, this does not work very well as AB begins in different positions in each row, viz.

Stay tuned! We will revisit this problem in a future blog!

That’s it for this week, tune in next week for more Power Pivot! Until then, happy pivoting!

Stay tuned for our next post on Power Pivot in the Blog section. In the meantime, please remember we have training in Power Pivot which you can find out more about here. If you wish to catch up on past articles in the meantime, you can find all of our past Power Pivot blogs here.