Table.MatchesAnyRows

D

T

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

Understanding the Table.MatchesAnyRows Function

Before we dive into the M code, let’s first take a quick look at what the Table.MatchesAnyRows function does. This function takes two arguments: a table and a condition. It then returns a Boolean value that indicates whether the table contains any rows that match the condition.

Here is an example of how to use this function:


let

Source = Table.FromRows({{"John", "Doe"}, {"Jane", "Doe"}, {"Bob", "Smith"}}, {"First Name", "Last Name"}),

MatchesAny = Table.MatchesAnyRows(Source, each [First Name] = "John")

in

MatchesAny


In this example, we create a table called “Source” with three rows and two columns: “First Name” and “Last Name”. We then use the Table.MatchesAnyRows function to check if the table contains any rows where the “First Name” column equals “John”. Since the table does contain such a row, the function returns True.

The M Code Behind Table.MatchesAnyRows

Now that we understand what the Table.MatchesAnyRows function does, let’s take a closer look at the M code behind it. Here is the M code for the function:


(Table as table, Condition as function) as logical =>

List.Contains(

List.Transform(

Table.ToList(Table.SelectColumns(Table.AddColumn(Table.DemoteHeaders(Table), "Index", each Table.PositionOf(Table, _){0}), "Index")),

Condition

),

true

)


As you can see, the function takes two arguments: “Table” and “Condition”. The “Table” argument is a table, and the “Condition” argument is a function that takes a record as input and returns a Boolean value.

The function first adds an “Index” column to the table using the Table.AddColumn function. This column contains the position of each row in the table.

It then uses Table.DemoteHeaders to convert the column headers into a row of values, and Table.SelectColumns to select only the “Index” column. Table.ToList is then used to convert the resulting table to a list.

The List.Transform function is then used to apply the “Condition” function to each record in the list. This returns a list of Boolean values indicating whether each record matches the condition.

Finally, List.Contains is used to check if the list contains any true values. If it does, the function returns True. Otherwise, it returns False.

In this article, we have taken a closer look at the M code behind the Power Query M function Table.MatchesAnyRows. We have seen how this function works and how it can be used to check if a table contains any rows that match a certain condition. By understanding the M code behind this function, you can gain a deeper understanding of how Power Query works and how you can use it to manipulate your data in powerful ways.

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)