List.PositionOf Function in Power Query M (6 Easy Examples)

List.PositionOf finds where a value sits in a list and gives you back its position as a number. If the value isn’t there, it returns -1. Available in Excel (Power Query), Power BI Desktop, and Power BI Service.

Table of Contents

Syntax of List.PositionOf Function

List.PositionOf(list as list, value as any, optional occurrence as nullable number, optional equationCriteria as any) as any

where

list (required, list). The list you want to search.
value (required, any). The value to find inside list.
occurrence (optional, nullable number). Which match to report: Occurrence.First, Occurrence.Last, or Occurrence.All. Defaults to Occurrence.First when omitted.
equationCriteria (optional, any). Controls how two values are compared. Pass a comparer like Comparer.OrdinalIgnoreCase, a key selector, or your own (x,y)=>... lambda.

Returns: a number, the 0-based position of the value in the list. With Occurrence.All it returns a list of numbers. Returns -1 when the value is not found (or an empty list {} with Occurrence.All).

In plain terms, you hand it a list and a value, and it tells you the slot number where that value lives.

Example 1: Find the position of a value in a list

Find where "Wed" sits in a list of weekday abbreviations.

let
    Source = List.PositionOf({"Mon","Tue","Wed","Thu","Fri"},"Wed")
in
    Source

Result: 2

"Wed" is the third item, but positions start at 0, so the first item is 0 and "Wed" lands on 2.

Example 2: See what happens when the value isn’t there

Search the same list for "Sun", which isn’t in it.

let
    Source = List.PositionOf({"Mon","Tue","Wed","Thu","Fri"},"Sun")
in
    Source

Result: -1

There’s no "Sun" in the list, so you get -1 rather than an error. Check for -1 before you use the result as an index.

Example 3: Get every position a value appears at

Pass Occurrence.All to find all the spots where 18 shows up in a list of meter readings.

let
    Source = List.PositionOf({18,24,18,31,18,24},18,Occurrence.All)
in
    Source

Result: {0, 2, 4}

With Occurrence.All the return is a list, not a single number. Here 18 appears at positions 0, 2, and 4.

Example 4: Find the last match while ignoring case

Use Occurrence.Last with Comparer.OrdinalIgnoreCase to find the last "maple" regardless of capitalization.

let
    Source = List.PositionOf({"Maple","oak","MAPLE","birch","Maple"},"maple",Occurrence.Last,Comparer.OrdinalIgnoreCase)
in
    Source

Result: 4

The comparer treats "Maple", "MAPLE", and "maple" as equal, and Occurrence.Last reports the final one at position 4.

Example 5: Match with your own tolerance rule

Pass a (x,y)=>... lambda as equationCriteria to match the first reading within 0.2 of 7.5.

let
    Source = List.PositionOf({4.2,5.9,7.4,9.1},7.5,Occurrence.First,(x,y)=>Number.Abs(x-y)<=0.2)
in
    Source

Result: 2

The lambda counts two values as equal when they’re within 0.2 of each other. 7.4 is close enough to 7.5, so it matches at position 2.

Example 6: Find which row a value is in within a table column

A common job is figuring out which row holds a value. Pull a single column into a list, then search it.

Say you have an Inventory table with SKU and Item columns, and you want the row position of SKU "NUT-305".

Here is the starting data:

SKU	Item
BLT-200	Hex Bolt
WSH-110	Flat Washer
NUT-305	Lock Nut
SCR-420	Wood Screw

Extract the SKU column and find the position of "NUT-305":

let
    Source = Excel.CurrentWorkbook(){[Name="Inventory"]}[Content],
    SKUList = Source[SKU],
    Position = List.PositionOf(SKUList,"NUT-305")
in
    Position

This reads the SKU values as a list and returns where "NUT-305" sits.

Result: 2

"NUT-305" is in the third row, which is position 2 in the 0-based list.

Things to keep in mind with List.PositionOf

Positions are 0-based. The first item is 0, not 1. If you’re used to Excel’s MATCH, knock one off what you’d expect.
-1 means not found, and it won’t throw. A missing value quietly returns -1. Used as an index, -1 can point at the last element, so guard for it first.
Occurrence.All changes the return shape. First and Last give a single number; Occurrence.All gives a list, and no match returns {} instead of -1. Downstream steps, including list functions like List.Accumulate, have to handle both.
Matching is case-sensitive and type-sensitive by default. "DOG" won’t match "dog", and the number 1 won’t match the text "1". Pass a comparer for case, or Text.From to line up types.

Performance and query folding

List.PositionOf works on an in-memory list, so it doesn’t fold to the source. When you pull a column from a database table to search it, that column is loaded locally first. Fine for typical lists, but on very large columns prefer a foldable filter like Table.SelectRows if you only need to test for existence.

Common questions about List.PositionOf

What is the difference between List.PositionOf and List.Contains?

List.Contains returns true or false for whether a value exists. List.PositionOf returns where it is. Use List.Contains for a yes/no test, much like Text.Contains does for text, and reach for List.PositionOf when you need the index.

How is List.PositionOf different from List.PositionOfAny?

List.PositionOf searches for one value. List.PositionOfAny takes a list of candidate values and returns the position of the first list item that matches any of them.

List of All Power Query Functions

Related Power Query Functions / Articles: