Text.Replace

What is Power Query?

Power Query is a data transformation and cleaning tool that is included in Microsoft Excel and Power BI. It allows users to connect to various data sources, clean and transform data, and load it into Excel or Power BI for analysis. Power Query uses a functional language called M, which is used to create custom data transformations.

The Text.Replace Function

The Text.Replace function is used to replace a specific text value with another value. The syntax for the Text.Replace function is as follows:

The parameters for the Text.Replace function are as follows:

– text: The text string that you want to replace a value in.

– old_text: The text value that you want to replace.

– new_text: The new text value that will replace the old text value.

– comparer: An optional function that can be used to specify the comparison mode for the search.

Examples

To better understand how the Text.Replace function works, let’s look at some examples.

Example 1

Suppose we have a table that contains product names, and we want to replace the word “widgets” with “gadgets”. We can use the Text.Replace function to accomplish this as follows:

In this example, `#”PreviousStep”` refers to the previous step in the data transformation process, and `{“Product Name”}` refers to the column that we want to replace the value in.

Example 2

Suppose we have a text string that contains a phone number in the format “(555) 555-5555”, and we want to replace the dashes with spaces. We can use the Text.Replace function to accomplish this as follows:

In this example, we are replacing the dash “-” with a space ” “.

Example 3

Suppose we have a text string that contains an email address, and we want to replace the “@” symbol with a period “.”. We can use the Text.Replace function to accomplish this as follows:

In this example, we are replacing the “@” symbol with a period “.”.

The Text.Replace function is a powerful tool in Power Query that can help simplify complex data sets for analysis. By understanding the M code behind this function, you can use it to its full potential and transform your data with ease.

Power Query and M Training Courses by G Com Solutions (0800 998 9248)

Upcoming Courses

Contact Us