Remove List Items by Positions in Power Query

Once you get acquainted with Power Query, you will work with lists often. And there may come cases where removing items from a list based on their position is important. Unfortunately, there is no built-in function to remove items from a list by their index position. That’s why this article delves into how you can do that using a custom function.

Table of contents

• The Scenario
• Removing List Items by Index Position
• Create List.RemovePositions Function
• Conclusion

The Scenario

Consider a scenario where you’re dealing with a list as shown below:

{ "a", "b", "c", "a", "b", "c", "a", "b", "c" }

The list repeats the values “a”, “b”, and “c” three times. Now, in case you want to remove the 4th and 6th index positions from this list, you will run into trouble. The functions you might think of initially don’t achieve the goal:

• List.RemoveItems: removes all instances of the specified values in the list, not what we aim for, as we want to retain the other occurrences.
• List.RemoveMatchingItems: Relies on value matching, just like List.RemoveItems, but allows an equation criteria.
• List.RemoveRange: Good at removing adjacent values but fails when the items are scattered.

Removing List Items by Index Position

What if instead of removing the items we want to discard, we could retrieve the items we want to keep? In this way, we can make use of item selection. And we already know how to select multiple items from a list by their index position.

To do that, our first task is to find which items we want to keep.

let
  Source     = { "a", "b", "c", "a", "b", "c", "a", "b", "c" }, 
  RemoveN    = { 4, 6 }, 
  
// Creates a list of index numbers starting at 0
  Indexes    = List.Positions ( Source ),  // Output: { 0 .. 8 }
  
// Returns only which index numbers to keep
  KeepValues = List.Difference ( Indexes, RemoveN ) // Output: { 0..3, 5, 7..8 } 
in
  KeepValues

So, our ‘KeepValues’ argument now contains the positions we want to keep. All that’s now left is to select all these items and return them as a result.

let
  Source     = { "a", "b", "c", "a", "b", "c", "a", "b", "c" }, 
  RemoveN    = { 4, 6 }, 
  Indexes    = List.Positions ( Source ),  // Output: { 0 .. 8 }
  KeepValues = List.Difference ( Indexes, RemoveN ),  // Output: { 0..3, 5, 7..8 } 
  
// Retrieves all index positions specified in the 'keepvalues' variable.
  Result     = List.Transform ( KeepValues, each Source{_} )
in
  Result // output: { "a", "b", "c", "a", "c", "b", "c" }

Create List.RemovePositions Function

Now, let’s turn this logic into a function, making it reusable for future use. The function signature looks like this:

( list as list, removeIndexes as list ) as list =>
  let
    Source     = list, 
    RemoveN    = removeIndexes, 
    Indexes    = List.Positions ( Source ), 
    KeepValues = List.Difference ( Indexes, RemoveN ), 
    Result     = List.Transform ( KeepValues, each Source{_} )
  in
    Result

When calling this function, say fxListRemovePositions, we can easily remove items at the 2nd and 4th index positions from a list by writing:

fxListRemovePositions( { "a".."g" } , { 2, 4 } ) // Output: { "a", "b", "d", "f", "g" }

Conclusion

As you can see, this compact fxListRemovePositions function is useful for working with lists, providing an easy path to exclude items based on their index positions, and doing so with a simple, intuitive syntax. We may not have a native function, but this serves as a great alternative.