Table.PositionOf Function in Power Query

The Table.PositionOf function determines the position or positions of a row within a table. The function returns -1 if no occurrence is found in the given table. The position numbering in the table starts from 0, so it means first row specified position 0.

Syntax

Table.PositionOf(table as table, row as record, optional occurrence as any, optional equationCriteria as any) as any

The following are the parameters of the function:
• table: The input table.
• row: The row in the table to find the position of.
• occurrence: [Optional] Specifies which occurrences of the row to return. By default, first occurrence position is returned.
• equationCriteria: [Optional] Controls the comparison between the table rows.

Example:

Power Query M

let
  MyTable = Table.FromRecords(
    {
      [EmployeeID = 1, Name = "Ashish", Marks = 56],
      [EmployeeID = 2, Name = "Katrina", Marks = 130],
      [EmployeeID = 3, Name = "Alia", Marks = 38],
      [EmployeeID = 4, Name = "Vicky", Marks = 48],
      [EmployeeID = 5, Name = "Mohini", Marks = 28],
      [EmployeeID = 3, Name = "Alia", Marks = 38],
      [EmployeeID = 7, Name = "Esha", Marks = 100],
      [EmployeeID = 8, Name = "Anjali", Marks = 0]
    }
  ), 
  return = Table.PositionOf(MyTable, [EmployeeID = 3, Name = "Alia", Marks = 38])
in
  return

The output of the above code is 2.

Power Query M

let
  MyTable = Table.FromRecords(
    {
      [EmployeeID = 1, Name = "Ashish", Marks = 56],
      [EmployeeID = 2, Name = "Katrina", Marks = 130],
      [EmployeeID = 3, Name = "Alia", Marks = 38],
      [EmployeeID = 4, Name = "Vicky", Marks = 48],
      [EmployeeID = 5, Name = "Mohini", Marks = 28],
      [EmployeeID = 3, Name = "Alia", Marks = 38],
      [EmployeeID = 7, Name = "Esha", Marks = 100],
      [EmployeeID = 8, Name = "Anjali", Marks = 0]
    }
  ), 
  return = Table.PositionOf(MyTable, [EmployeeID = 3, Name = "Alia", Marks = 38], Occurrence.Last)
in
  return

The output of the above code is 5.

Power Query M

let
 MyTable = Table.FromRecords(
   {
     [EmployeeID = 1, Name = "Ashish", Marks = 56],
     [EmployeeID = 2, Name = "Katrina", Marks = 130],
     [EmployeeID = 3, Name = "Alia", Marks = 38],
     [EmployeeID = 4, Name = "Vicky", Marks = 48],
     [EmployeeID = 5, Name = "Mohini", Marks = 28],
     [EmployeeID = 3, Name = "Alia", Marks = 38],
     [EmployeeID = 7, Name = "Esha", Marks = 100],
     [EmployeeID = 8, Name = "Anjali", Marks = 0]
   }
 ), 
 return = Table.PositionOf(MyTable, [EmployeeID = 3, Name = "Alia", Marks = 38], Occurrence.All)
in
 return

The output of the above code is shown in the image below:

Table.PositionOf function in Power Query
Previous Next

Power Query M