List.ReplaceRange - List Function

List.ReplaceRange is a Power Query M function that replaces a specified number of values in a list with another list, starting at a specified position. The function returns a modified list with the specified range replaced.

Compatible with: Power BI Service Power BI Desktop Excel Microsoft 365

Syntax

List.ReplaceRange(
   list as list,
   index as number,
   count as number,
   replaceWith as list,
) as list

Description

The List.ReplaceRange function replaces a specific segment within a list with a new set of values. The function operates by removing the specified range of elements from the original list and then inserting the new values at the same starting index. It returns a new list with the specified segment replaced, maintaining the original order of the unaffected elements.

Examples

Basic Range Replacement

Suppose you have a list { 1, 1, 1, 4, 5 }. To replace a range starting from the second element (1 at index 1) with the values { 2, 3 }, the function is used as follows:

// Returns { 1, 2, 3, 4, 5 }
List.ReplaceRange( { 1, 1, 1, 4, 5 }, 1, 2, { 2, 3 } )

This shows List.ReplaceRange can selectively replace a specific segment within a list.

Text Value Replacement

When working with text values, such as replacing the first two elements of { "A", "B", "Dan", "Ace" } with { "Avi", "Ben" }, the function operates similarly:

// Returns { "Avi", "Ben", "Dan", "Ace" }
List.ReplaceRange( { "A", "B", "Dan", "Ace" }, 0, 2, { "Avi", "Ben" } )

Regardless of the data type within the list, the function can replace its contents.

Replacing More Values Than Inserted

List.ReplaceRange can also replace a larger range of values with a smaller number of new values:

// Returns { "Avi", "Ben" }
List.ReplaceRange( { "A", "B", "Dan", "Ace" }, 0, 4, { "Avi", "Ben" } )

Here, four elements are replaced by only two, showing how the operation can also remove values.

Inserting More Values Than Replaced

Conversely, the function can insert more values than it replaces:

// Returns { "Aad", "Bas", "Cas", "Gia", "Dan", "Ace" }
List.ReplaceRange( { "A", "B", "Dan", "Ace" }, 0, 2, { "Aad", "Bas", "Cas", "Gia" } )

That means List.ReplaceRange can be used to extend the list by inserting additional elements in place of a smaller number of existing ones.

Learn more about List.ReplaceRange in the following articles:

Lists in Power Query M / List Functions (200+ Examples)
The complete guide to Lists in Power Query M. Learn from practical examples and master Power Query’s most powerful List functions. » Read more

Other functions related to List.ReplaceRange are:

Contribute » | Contributors: Rick de Groot
Microsoft documentation: https://learn.microsoft.com/en-us/powerquery-m/list-replacerange