List.PositionOf

Updated on
Rick de Groot

List.PositionOf is a Power Query M function that returns the offset at which a value appears in a list. The function returns the offset or -1 if the value doesn’t appear, with an optional occurrence parameter.

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

Syntax

List.PositionOf(
   list as list,
   value as any,
   optional occurrence as nullable number,
   optional equationCriteria as any,
) as any
ArgumentAttributeDescription
listThe list you want to inspect.
valueThe value to search for in the list.
occurrenceoptionalThe Occurrence.Type specifies the occurrence of an element in a sequence. When omitted, Power Query defaults to returning the first match with Occurrence.First. Alternatively, you can return the last (Occurrence.Last) or all matches it finds (Occurrence.All).
equationCriteriaoptionalUses Comparer Functions to determine how values are equated during operations. Options include Comparer.Ordinal for exact case-sensitive matching, Comparer.OrdinalIgnoreCase for case-insensitive matching, and Comparer.FromCulture for culture-specific comparisons.

Description

The List.PositionOf function is used to find the index at which a specific value occurs in a given list. If the value you’re looking for doesn’t appear in the list, the function returns -1.

You can also add optional parameters to specify whether you’re interested in the first occurrence, the last, or all occurrences of that value.

Additional optional parameters allow for customized comparison methods, such as case-sensitive or culture-specific comparisons.

Examples

Basic Index Search

If you have a simple list like {1, 2, 3, 4, 5} and you’re wondering where the number 3 appears, you’d use the function like this:

// Output: 2
List.PositionOf( { 1, 2, 3, 4, 5 }, 3 )

The number 3 appears at the third position in the list, but since index numbers start from zero, the function returns 2.

When a Value is not Present

If you look for a number that isn’t in the list, you’ll get -1 as the output. For example, searching for 7 in the list {1, 2, 3, 4, 5} would look like this:

List.PositionOf({1, 2, 3, 4, 5}, 7) // Output: -1

This tells you that the value doesn’t exist in the list.

Specifying the Occurrence Type

The following examples show how you can make sure of the Occurrence.Type parameter.

Finding the First Occurrence

What if the value appears more than once and you’re interested in the first occurrence? By default, List.PositionOf will give you the first matching index. So, if you have a list like {3, 2, 1, 1, 2, 3}, you’d get:

List.PositionOf({3, 2, 1, 1, 2, 3}, 3, Occurrence.First ) // Output 0

Finding the Last Occurrence

You can change this behavior and ask for the last occurrence by specifying Occurrence.Last. 

List.PositionOf({3, 2, 1, 1, 2, 3}, 3, Occurrence.Last) // Output 5

Getting All Occurrences

Or maybe you want to know all the spots where the value shows up. You can do this by using Occurrence.All:

List.PositionOf({3, 2, 1, 1, 2, 3}, 3, Occurrence.All) // Output { 0, 5 }

The impact of the equationCriteria

The equationCriteria allows you to change the rules for the comparison by using the Occurrence.Type.

Case-Sensitive Search

By default, text comparisons are case-sensitive and use Comparer.Ordinal, even if you omit this argument So, if you search for a capitalized “A” in a list with a lowercase “a”, you won’t find it:

List.PositionOf({"a", "b", "c"}, "A", null, Comparer.Ordinal) // Output: -1

Case-Insensitive Search

However, you can make your search case-insensitive by using Comparer.OrdinalIgnoreCase:

List.PositionOf({"a", "b", "c"}, "A", null, Comparer.OrdinalIgnoreCase) // Output: 0

Culture-specific Comparison

Different cultures treat characters differently. For example, the Danish culture treats the character “æ” as distinct from “ae”. Here’s how you could search using a culture-specific comparison using Comparer.FromCulture:

List.PositionOf( {"æ", "b", "c"}, "ae", null, Comparer.FromCulture("da-DK") ) // Output: -1

In contrast, the English language considers “æ” and “ae” to be identical. Here’s how that search would look:

List.PositionOf( {"æ", "b", "c"}, "ae", null, Comparer.FromCulture("en-US") ) // Output 0

Real Life Scenario: Determine Sentence Case

Let’s delve into a practical example that you might encounter when dealing with text data. Imagine you have a data table and one of its columns is full of sentences.

List.PositionOf Example dataset with sentence casing

These sentences can have different types of casing:

  • All Lowercase
  • Sentence Case
  • Capitalize Each Word
  • All Capitals
  • Mixed Case

Your task is to identify the casing style for each sentence in that column. To tackle this challenge, you can cleverly use List.PositionOf.

The first step is to generate a list of boolean (true or false) values for each sentence based on various casing criteria. For example:

{ Text.Upper(s) = s, Text.Proper(s) = s, Text.Lower(s) = s, a = Text.Lower(a), true }

Here, s represents the original sentence, and a represents the sentence without the first character. Each item in the list is a comparison that returns true or false. The last item in the list is just a true value as a catch-all.

You can visually represent that as:

List.PositionOf Result of tests return boolean values

Next, you would use List.PositionOf to find the index position of the first true value in the list. This will tell you which casing style the sentence follows:

List.PositionOf(
 { Text.Upper(s) = s, Text.Proper(s) = s, Text.Lower(s) = s, a = Text.Lower(a), true }, 
 true
)

Once you have the index position, you can then map it to a predefined list of sentence casing styles:

Case = {"All Caps", "Start Case", "All Lowercase", "Sentence Case", "Mixed Case"}{index}

The index here is the position found using List.PositionOf.

Finally, here’s how you’d integrate all of this into your Power Query code:

Table.AddColumn(
  Source, 
  "Sentence Casing", 
  each 
   let
    a = Text.Middle([Sentence], 1), 
    s = [Sentence], 
    index = 
      List.PositionOf(
       { Text.Upper(s) = s, Text.Proper(s) = s, Text.Lower(s) = s, a = Text.Lower(a), true }, 
       true
      ), 
    Case = {"All Caps", "Start Case", "All Lowercase", "Sentence Case", "Mixed Case"}{index}
   in
    Case
 )

The code uses Table.AddColumn to create a new column in the table (Source) named “Sentence Casing”. Each row in this new column will contain the type of casing found in the original “Sentence” column.

List.PositionOf expression returning sentence casing

And that’s how you can flexibly and efficiently identify sentence casing using List.PositionOf in Power Query. You’ve essentially built a mini casing identifier tool right within your data transformation process!

Learn more about List.PositionOf in the following articles:

Other functions related to List.PositionOf are:

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

2023-2024 © BI Gorilla. All rights are reserved. Information from Microsoft docs is property of Microsoft Corp. | Privacy Policy