List.SelectPositions in Power BI and Power Query

With this new custom function “List.SelectPositions” you can easily select items from a list by just passing a list of their positions within it as the parameter.

What it does

Say you have a list with numbers {1..5} and want to select the 1st, 4th and 5th element from it. Then you can pass these positions to the function as another list: {0, 3, 4}.

ListSelectPositions({1..5}, {0, 3, 4}) will return: {1,4,5}

You see that I’ve decided to follow the zero-based counting principle here, that you find throughout M in the query editor. If you don’t like that, you can use the optional 3rd parameter to let it start to count from 1 instead:

ListSelectPositions({1..5}, {1, 4, 5}, 1) will return {1, 4, 5}

But if you have entered positions that don’t exist, the function will return an error in their positions by default:

ListSelectPositions({1..5}, {1, 4, 5}) will return {2, 5, Error}

because there is no 6th element (you’ve omitted the 3rd parameter that allows you to start counting with 1).

But you can change this behaviour as well through the last optional 4th parameter: Setting it to 0 will fill the missing positions with null like this:

ListSelectPositions({1..5}, {1, 4, 5}, null, 0) will return {2, 5, null}

and setting it to 1 will eliminate it and shorten the list like this:

ListSelectPositions({1..5}, {1, 4, 5}, null, 1) will return {2, 5}

These additional error-handling-options of the 4th parameters are useful for dealing with badly formatted data and if you want to learn more about it, just let me know in the comments so that I can prioritize it.

Function code


let func =
(SelectionList as list, ListOfPositions as list, optional StartWithOne as number, optional FillWithNulls as number) =>
let
selectionList = List.Buffer(SelectionList),
listOfPositions = ListOfPositions,
StartPosition = if StartWithOne = 1 then -1 else 0,
Result = {List.Transform(listOfPositions, each selectionList{_ + StartPosition}), // null
List.Transform(listOfPositions, each selectionList{_+StartPosition}?), // 0
Table.RemoveRowsWithErrors(Table.FromColumns({List.Transform(listOfPositions, each selectionList{_+StartPosition})})) //1
}{List.PositionOf({null,0,1}, FillWithNulls)}
in
Result
, documentation = [
Documentation.Name = " List.SelectPositions
", Documentation.Description = " Selects all items from <code>SelectionList</code> that are on positions in <code>ListOfPositions</code>.
" , Documentation.LongDescription = " Selects all items from <code>SelectionList</code> that are on positions in <code>ListOfPositions</code>. For the behaviour of the optional parameters see: https://wp.me/p6lgsG-N8 .
", Documentation.Category = " List
", Documentation.Source = " https://wp.me/p6lgsG-N8 .
", Documentation.Author = " Imke Feldmann: www.TheBIccountant.com .
", Documentation.Examples = {[Description = " For details see: https://wp.me/p6lgsG-N8 .
" , Code = " List.SelectPositions({1..5}, {0, 3, 4})
", Result = " {1,4,5}
"]}]
in
Value.ReplaceType(func, Value.ReplaceMetadata(Value.Type(func), documentation))

view raw

List.SelectPositions.pq

hosted with ❤ by GitHub

How it works

The core of this function is List.Transform. It is an iterator that goes through every item of the list and does something to it. In our case it passes the SelectionList (full list) as the first argument to each item of itself (as the index) to it: SelectionList{EachItemOfListOfPositions}. The M-code for this looks like so:

List.Transform(ListOfPositions, each SelectionList{_})

The first argument of List.Transform is the list whose items are going to be iterated through and the second argument specifies what’s being done to each item (hence the “each”-keyword). For each item, the SelectionList will be referenced and one of its items will be selected: Actually, that one whose position is given by the current item from the ListOfPositions.

So you have to twist your head a bit here, as the logic-flow is a bit counter-intuitive.
(edit: Seems as I was wrong with counter-intuition, as Rick de Groot comes up with exact the same approach over 5 years later here: 3 Ways to Select List Items by Position in Power Query (gorilla.bi))

The rest of the function code is either documentation (row 12 onward) or adjustments for the optional parameters.

Enjoy and stay queryious 😉

Comments (5) Write a comment

  1. Hi Imke,

    regarding your 4th parameter, how many lists will be calculated? I’m not sure how lazy M is. Will it calculate just one list or will it select one list out of three? Only in the latter case buffering the input lists probably makes sense!?

    There’s a typo btw, the results step does not use the buffered lists.

    Reply

    • Thanks Frank,
      according to the Language specification: “List and record member expressions (as well as let expressions, introduced further below) are evaluated using lazy evaluation, which means that they are evaluated only as needed. All other expressions are evaluated using eager evaluation, which means that they are evaluated immediately, when encountered during the evaluation process. ”

      Thanks for pointing out the typos – you see that I haven’t checked the impact of the buffer. Removed it from the ListOfPositions, but pretty sure that they make sense for the SelectionList, as it is referenced multiple times during the List.Transform-operation.
      Cheers, Imke

      Reply

  2. Pingback: A generic SWITCH-function for the query editor in Power BI and Power Query – The BIccountant

  3. Dear Imke,
    Thank you very much. Coming from a Python-background and being used to working with List Comprehension this code is very insightful.
    One question: what is the added value of line 6?

    //JW

    Reply

    • Dear Jan Willem,
      thank you !
      The code in row 6 is just for debugging-purposes: It allows me to reference a specific list (once) without having to replace its occurrence in the code itself.
      /Imke

      Reply

Leave a Reply