Table.PositionOf

D

Back to top

T

Back to top

#

Back to top

The M Code Behind the Power Query M function Table.PositionOf

What is Table.PositionOf?

Table.PositionOf is an M function in Power Query that enables you to determine the position or index of a given value within a table column. The function takes two arguments; the first argument is the table column you want to search, while the second is the value you want to find. Table.PositionOf returns the index of the first occurrence of the value in the table column.

Syntax

The syntax for Table.PositionOf is as follows:

Table.PositionOf(table as table, value as any, optional equationCriteria as any) as nullable number

Parameters

Table.PositionOf takes the following parameters:

– table: The table to search

– value: The value to search for

– optional equationCriteria: A comparison operator to use to match the value. This parameter is optional.

How to use Table.PositionOf

You can use the Table.PositionOf function in Power Query to determine the index of a value in a table column. Here is an example of how to use Table.PositionOf:

Suppose you have a table named Sales that contains columns for Product, Region, and Sales Quantity. To find the position of the value “East” in the Region column, you can use the following M code:

let

Source = Sales,

Position = Table.PositionOf(Source[Region], "East")

in

Position

The M code above returns the index of the first occurrence of “East” in the Region column of the Sales table.

Using optional equationCriteria parameter

You can also use the optional equationCriteria parameter with Table.PositionOf to specify a comparison operator. For example, suppose you have a table named Inventory that contains columns for Product, Quantity, and Price. To find the position of the value 0 in the Quantity column where the price is greater than 100, you can use the following M code:

let

Source = Inventory,

Position = Table.PositionOf(Source[Quantity], 0, (x, y) => x > 100)

in

Position

The M code above returns the index of the first occurrence of 0 in the Quantity column of the Inventory table where the Price is greater than 100.

The Table.PositionOf function in Power Query is a powerful tool that can help you determine the index of a value in a table column. It is easy to use and can save you a lot of time when working with large datasets. Whether you are a data analyst or data scientist, understanding the M code behind the Table.PositionOf function can help you extract and transform data in a way that suits your needs.

Power Query and M Training Courses by G Com Solutions (0800 998 9248)

Upcoming Courses

Contact Us

    Subject

    Your Name (required)

    Company/Organisation

    Email (required)

    Telephone

    Training Course(s)

    Your Message

    Upload Example Document(s) (Zip multiple files)