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
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:
