Table.PositionOfAny Function in Power Query
The Table.PositionOfAny function determines the position or positions of any of the specified rows within the table.
Syntax
Table.PositionOfAny(table as table, rows as list, optional occurrence as nullable number, optional equationCriteria as any) as any
Example:
Power Query M
let
MyTable = Table.FromRecords(
{
[EmployeeID = 1, Name = "Ashish", Science_Marks = 56, Math_Marks=65],
[EmployeeID = 2, Name = "Katrina", Science_Marks = 130, Math_Marks=65],
[EmployeeID = 3, Name = "Alia", Science_Marks = 38, Math_Marks=65],
[EmployeeID = 4, Name = "Vicky", Science_Marks = 48, Math_Marks=90],
[EmployeeID = 5, Name = "Mohini", Science_Marks = 28, Math_Marks=0],
[EmployeeID = 6, Name = "Meenakshi", Science_Marks = -29, Math_Marks=65],
[EmployeeID = 7, Name = "Esha", Science_Marks = 100, Math_Marks=50],
[EmployeeID = 8, Name = "Anjali", Science_Marks = 0, Math_Marks=38]
}
),
return = Table.PositionOfAny(MyTable,
{[EmployeeID = 2, Name = "Katrina", Science_Marks = 130, Math_Marks=65],
[EmployeeID = 5, Name = "Mohini", Science_Marks = 28, Math_Marks=0],
[EmployeeID = 7, Name = "Esha", Science_Marks = 100, Math_Marks=50]
})
in
return The output of the above code is 1.
Example:
Power Query M
let
MyTable = Table.FromRecords(
{
[EmployeeID = 1, Name = "Ashish", Science_Marks = 56, Math_Marks=65],
[EmployeeID = 2, Name = "Katrina", Science_Marks = 130, Math_Marks=65],
[EmployeeID = 3, Name = "Alia", Science_Marks = 38, Math_Marks=65],
[EmployeeID = 4, Name = "Vicky", Science_Marks = 48, Math_Marks=90],
[EmployeeID = 5, Name = "Mohini", Science_Marks = 28, Math_Marks=0],
[EmployeeID = 6, Name = "Meenakshi", Science_Marks = -29, Math_Marks=65],
[EmployeeID = 7, Name = "Esha", Science_Marks = 100, Math_Marks=50],
[EmployeeID = 8, Name = "Anjali", Science_Marks = 0, Math_Marks=38]
}
),
return = Table.PositionOfAny(MyTable,
{ [EmployeeID = 2, Name = "Katrina", Science_Marks = 130, Math_Marks=65],
[EmployeeID = 5, Name = "Mohini", Science_Marks = 28, Math_Marks=0],
[EmployeeID = 7, Name = "Esha", Science_Marks = 100, Math_Marks=50]
}, Occurrence.All)
in
return The output of the above code is shown below:
