3 Ways to Select List Items by Position in Power Query

Retrieving specific items from a list based on their position is a common requirement in Power Query. Despite having many list functions, the M language does not have a built-in function to achieve this task directly.

Users are left to build their own logic to achieve this task. This article explores 3 different ways to select multiple items in your list by index position.

Table of contents

• Importance of Selecting Nth Items
• Select a Single Item from a List
• Select Multiple Items from a List
  • Using List.Transform
  • Adding Indexes to List
  • Switching List to Table
• Creating List.SelectPositions Function
• Conclusion

Importance of Selecting Nth Items

Extracting specific items from a list is a common requirement. For instance, you might be looking to transform the 2nd and 4th columns of your table. Here, you would need to select these items from the output of the Table.ColumnNames function.

Or perhaps you have a function that returns you the index numbers of the desired characters you want to retrieve. Here, too, you would need a way to retrieve the items. So how can we do that?

Select a Single Item from a List

To lay the groundwork, let’s explore a simple scenario. Suppose you have a list with the letters a to z.

{ "a" .. "z" }

Now, let’s say you want to grab the 11th letter from this list. There’s a simple operator to help you get one item from the list. This is how you use the positional index operator:

{ "a" .. "z" }{10} // output: "k"

Select Multiple Items from a List

Now, imagine you want to grab the 11th, 15th, and 20th letters from this list. To get all positions from this code, you would have to perform this action multiple times.

The coming section shows you three different ways to do that. Each of these methods tackles the problem from a different angle, giving us more than one way to get the job done.

Using List.Transform

The first method uses the List.Transform function. It starts out with a list of the positions you want to retrieve. For each value in the list, it takes the original alphabet list and retrieves the Nth item.

// Output: { "k", "p", "u" }
let
  myList = { "a" .. "z" },
  Positions = { 10, 15, 20 },
  FetchItems = List.Transform( Positions,  each myList{ _ } )
in
  FetchItems

Using this construct, you can then retrieve column names by position by writing:

let
  myList = Table.ColumnNames( PreviousStep ),
  Positions = { 2, 5, 6 },
  FetchItems = List.Transform( Positions,  each myList{ _ } )
in
  FetchItems

In that way, you can always refer to a column name by its position instead of hardcoding it.

Adding Indexes to List

The third method adds an index number to each value in the list. The List.Positions can be used to retrieve all index numbers of the list items. Adding both this list and the original list in a List.Zip function then gives you a list of lists containing both the original value and an index number.

For each item in the resulting list, the List.Transform function checks whether the index number equals one of the Positions.

// Output: { "k", "p", "u" }
let
  myList        = { "a" .. "z" }, 
  Positions     = { 10, 15, 20 }, 
  AddIndex      = List.Zip ( { myList, List.Positions ( myList ) } ), 
  IdentifyItems = 
    List.Transform (
      AddIndex, 
      each if List.Contains ( Positions, _{1} ) then _{0} else null
    ), 
  RemoveNulls   = List.RemoveNulls ( IdentifyItems )
in
  RemoveNulls

Switching List to Table

Another method involves turning the list into a table. In that shape, you can easily add an index column and filter this column on only the relevant index positions.

It is similar to the above code, but many users are more familiar with table functions. Adding an index then involves Table.AddIndexColumn, after which you can select the relevant rows by using the Table.SelectRows function.

To test whether the rows equal one of a selection of positions, we mimic the IN operator.

// Output: { "k", "p", "u" }
let
  myList = { "a" .. "z" },
  Positions = {10, 15, 20 },
  ListToTable = Table.FromColumns( { myList } ),
  AddIndex = Table.AddIndexColumn( ListToTable, "Index", 0, 1, Int64.Type ),
  SelectPositions = Table.SelectRows(AddIndex, each List.Contains( Positions, [Index] ) ),
  ReturnColumn = SelectPositions[Column1]
in
    ReturnColumn

Creating List.SelectPositions Function

Now, the logic above is good for educational purposes, but most of us simply want to apply it to our values. To do that, we can turn the code into a function. Since method 1 has my preference, we will use that one. Here’s the function code:

( inputList as list, positions as list ) as list =>
let
  myList = inputList,
  BufferList = List.Buffer( myList ),
  Positions = positions,
  FetchItems = List.Transform( Positions,  each BufferList { _ } )
in
  FetchItems

Call this function fxListSelectPositions, and each time you want to fetch the Nth item from a list, you can simply write:

fxListSelectPositions( {"a".."z"}, {10, 15, 20 } )

Conclusion

Each of these methods shows a way to select multiple items in a list in Power Query based on their index positions. Although it requires building your own logic, the process is straightforward and helps you build dynamic queries.