Replacer.ReplaceText

D

Back to top

T

Back to top

#

Back to top

The M Code Behind the Power Query M function Replacer.ReplaceText

The Replacer.ReplaceText function in Power Query M Language is used to replace a specific text string with another text string. This function is very useful for cleaning up data, especially when working with large datasets.

The syntax for the Replacer.ReplaceText function is as follows:

= Table.ReplaceValue(table as table, old_value as any, new_value as any, replacer as function, column_names as any, optional options as nullable record)

In this syntax, the `table` argument is the input table that needs to be transformed. The `old_value` argument is the text string that needs to be replaced. The `new_value` argument is the text string that will replace the old value.

The `replacer` argument is a function that is used to replace the text string. This function takes two arguments: the old value and the new value. The `column_names` argument specifies which columns in the table will be searched for the old value. The `options` argument is optional and is used to specify additional options for the function.

How to Use the Replacer.ReplaceText Function

Here is an example of how to use the Replacer.ReplaceText function in Power Query:

Suppose we have a table named `Sales` that contains sales data for a company. The table has two columns: `Product` and `SalesAmount`. The `Product` column contains the names of the products that were sold, and the `SalesAmount` column contains the amount of sales for each product.

We want to replace the name of one of the products, say "Product A", with "Product B". To do this, we need to use the Replacer.ReplaceText function. Here is the M code to achieve this:

let

Source = Sales,

ReplaceProdA = Table.ReplaceValue(Source,”Product A”,”Product B”,Replacer.ReplaceText,{“Product”})

in

ReplaceProdA

In this code, we first create a variable named `Source` that contains the `Sales` table. We then use the Table.ReplaceValue function to replace the text "Product A" with "Product B" in the `Product` column of the `Sales` table.

Using the Replacer.ReplaceText Function with Regular Expressions

One of the most powerful features of the Replacer.ReplaceText function is that it can be used with regular expressions. Regular expressions are a sequence of characters that define a search pattern. They are used in many programming languages and tools to search, manipulate, and validate text.

Here is an example of how to use the Replacer.ReplaceText function with regular expressions:

Suppose we have a table named `Employees` that contains employee data for a company. The table has three columns: `Name`, `Title`, and `Salary`. We want to replace all the titles that start with "Manager" with "Director". We can use regular expressions to achieve this.

Here is the M code to achieve this:

let

Source = Employees,

ReplaceManager = Table.ReplaceValue(Source,”^Manager.$”,”Director”,Replacer.ReplaceText,{“Title”})

in

ReplaceManager

In this code, we use the regular expression "^Manager.$" to match all titles that start with "Manager". The "^" character matches the beginning of a string, the "." matches any number of characters, and the "$" matches the end of a string. We then replace the matched text with "Director" using the Replacer.ReplaceText function.

The Replacer.ReplaceText function in Power Query M Language is a powerful tool for cleaning up data. It can be used to replace text strings with other text strings, and it supports regular expressions for more complex replacements. By understanding the M code behind this function, you can use it to transform and analyze data with more precision and accuracy.

Power Query and M 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)