List.ReplaceRange

Here, the `list` parameter is the input list that we want to modify. The `offset` parameter is the position within the list where we want to start replacing values. The `count` parameter is the number of values that we want to replace. Finally, the `newItems` parameter is the new set of values that we want to replace the old values with.

Using the List.ReplaceRange Function

Now that we understand the syntax of the List.ReplaceRange function, let's look at an example of how it can be used to modify data within Power Query.

Suppose we have a table containing sales data for a company. The table contains columns for the month, product, and sales amount. We want to replace the sales amount for the month of January with a new value of 1000. We can use the List.ReplaceRange function to achieve this.

First, we need to select the sales amount column and convert it to a list. We can do this using the following M code:

Here, `[SalesData]` is the name of the table that contains our sales data, and `"SalesAmount"` is the name of the column that contains the sales amount data.

Next, we can use the List.ReplaceRange function to replace the sales amount for the month of January. We can do this using the following M code:

Here, `salesAmount` is the list of sales amounts that we obtained in the previous step. We want to replace the first value in the list (which corresponds to the sales amount for January), so we set the `offset` parameter to 0. We want to replace only 1 value, so we set the `count` parameter to 1. Finally, we want to replace the old value with a new value of 1000, so we set the `newItems` parameter to `{1000}`.

After running this code, we will have a new list of sales amounts where the value for January has been replaced with 1000. We can then use this new list to update our original sales data table.

The List.ReplaceRange function is a powerful tool within the Power Query M language that allows users to modify data within a list. By understanding the syntax and usage of this function, we can easily manipulate data within Power Query to meet our needs. Whether we are replacing values within a list or updating entire tables, the List.ReplaceRange function can help us achieve our goals efficiently and effectively.

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

Upcoming Courses

Contact Us