Table.PositionOfAny

In this article, we will dive deep into the M code behind the Table.PositionOfAny function and explore how it can be used to extract data from tables in Power Query.

Understanding the Table.PositionOfAny Function

The Table.PositionOfAny function is used to find the position of a value within a column of a table. This function takes two parameters: the column and the list of values to search for.

The syntax for the Table.PositionOfAny function is as follows:

– `table`: The table to search for values.

– `column`: The name of the column to search.

– `values`: The list of values to search for.

– `missingValue`: (Optional) The value to return if the searched value is not found.

The function returns a list of positions for each value found in the searched column.

How to Use Table.PositionOfAny in Power Query

To use the Table.PositionOfAny function in Power Query, we need to create a query that returns a table with the column we want to search.

Let’s say we have a table named “Sales” with the following columns: “Date”, “Product”, “Sales Amount”. We want to find the position of the first occurrence of the values “Apples” or “Oranges” in the “Product” column.

To accomplish this, we need to create a new query that references the “Sales” table and returns only the “Product” column:

Now that we have a query that returns only the “Product” column, we can use the Table.PositionOfAny function to find the position of “Apples” or “Oranges” within the column:

The output of this query will be a list of positions where “Apples” or “Oranges” were found in the “Product” column.

Additional Parameters for Table.PositionOfAny

The Table.PositionOfAny function also accepts an optional parameter called “missingValue”. This parameter specifies the value to return if none of the searched values are found in the column.

For example, let’s say we want to find the position of “Pears” within the “Product” column. However, “Pears” are not listed in the table. In this case, we can use the “missingValue” parameter to return a custom value instead of an error:

In this example, we have set the “missingValue” parameter to -1. If “Pears” are not found in the “Product” column, the function will return -1 instead of an error.

The Table.PositionOfAny function is a powerful tool for searching tables in Power Query. By understanding the M code behind this function, we can use it to extract data from tables quickly and efficiently. Whether you are a beginner or an experienced Power Query user, the Table.PositionOfAny function is a valuable tool to have in your arsenal.

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

Upcoming Courses

Contact Us