Table.ReplaceMatchingRows

One such function is the Table.ReplaceMatchingRows function, which allows you to replace rows in a table that match a specified condition. In this article, we will explore the M code behind this function and how to use it effectively.

Understanding the Table.ReplaceMatchingRows Function

The Table.ReplaceMatchingRows function is used to replace rows in a table that match a specified condition. This function takes three arguments:

1. table: This is the table that you want to modify.

2. condition: This is the condition that you want to use to match the rows that you want to replace.

3. replacement: This is the replacement row that you want to use to replace the matching rows.

The syntax of the function is as follows:

Using the Table.ReplaceMatchingRows Function

To use the Table.ReplaceMatchingRows function, you need to follow these steps:

1. Open the Power Query Editor.

2. Select the table that you want to modify.

3. Click on the “Add Column” tab and select “Custom Column”.

4. Enter a name for the new column.

5. In the “Custom Column Formula” dialog box, enter the Table.ReplaceMatchingRows function with the appropriate arguments.

6. Click “OK” to create the new column.

For example, suppose you have a table named “Sales” that contains the following columns: “Region”, “Product”, “Salesperson”, and “Sales”. You want to replace all rows where the “Region” column is equal to “West” with a new row that has the “Region” column set to “Midwest”. To do this, you can use the following formula in the “Custom Column Formula” dialog box:

This formula creates a new column that replaces all rows where the “Region” column is equal to “West” with a new row that has the “Region” column set to “Midwest”.

Understanding the M Code Behind the Function

The M code behind the Table.ReplaceMatchingRows function is quite complex, but it can be broken down into several steps:

1. The function takes three arguments: “table”, “condition”, and “replacement”.

2. The “table” argument is converted to a record.

3. The “condition” argument is converted to a function.

4. The “replacement” argument is converted to a record.

5. A new table is created by iterating over each row in the original table.

6. For each row, the “condition” function is applied to determine if the row should be replaced.

7. If the “condition” function returns true, the “replacement” record is used to replace the current row.

8. The new table is returned.

Tips for Using the Table.ReplaceMatchingRows Function

Here are some tips for using the Table.ReplaceMatchingRows function effectively:

1. Use the function sparingly: The Table.ReplaceMatchingRows function can be slow and memory-intensive, so use it only when necessary.

2. Test your condition carefully: Make sure that your condition correctly identifies the rows that you want to replace.

3. Check your replacement carefully: Make sure that your replacement record contains all the necessary columns and has the correct data types.

4. Use the function in combination with other functions: The Table.ReplaceMatchingRows function can be used in combination with other functions, such as the Table.SelectRows function, to create more complex transformations.

The Table.ReplaceMatchingRows function is a powerful tool that allows you to replace rows in a table that match a specified condition. By understanding the M code behind the function and following the tips outlined in this article, you can use this function effectively to transform your data and create more insightful reports.

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

Upcoming Courses

Contact Us