Please note javascript is required for full website functionality.
MVP

Blog

Power Query: Is it on the Table?

19 June 2019

Welcome to our Power Query blog.  Today, I delve into the Table.Contains() M functions.

 

I have a list of companies that my imaginary salespeople have interacted with recently.  I have linked to a ‘Contacts’ table, and I want to use that to find out which companies John has contacted.

I can expand the table information, but even for this small example, that would create extra rows I don’t need to see.  There is another way:

Table.Contains table as table, row as record, optional equationCriteria as any) as logical


This determines whether a record appears as a
row in the table.

Table.Contains is similar to List.Contains, which I previously looked at in Power Query: Words are Key.

I add a custom column from the ‘Add Column’ tab. 

The M code I have used is:

= Table.Contains([Contacts], [Name="John"])

In order to look for “John”, I need to enter the record that would feature the name John.  When I click OK, a new column is created:

I can now expand the Contacts column to check my results. 

It is harder to read as there are extra rows, but I can see that John has contacted the first three companies.

I can also look at it a different way. Going back to my merged table, I want to see if any of the companies were contacted by John and Mary. To do this, I am going to use Table.ContainsAll():

Table.ContainsAll(table as table, rows as list, optional equationCriteria as any) as logical

This determines whether all of the specified records appear as rows in the table. 

In this case, I enter my employee names as a list of records. I can use this function to look for more than one employee contacting (i.e. harassing!) a company.

The M code I have used is:

= Table.ContainsAll([Contacts], {[Name="John"], [Name="Mary"]})

When I press OK, I can see who has been contacted by both John and Mary:

Tent Time USA appears to have been contacted by both of them, and I can expand the table to verify this.

I can see that the company were indeed contacted by both salespeople.

I can also check if a company has been contacted by any of my salespeople, to do this I use Table.ContainsAny:

Table.ContainsAny(table as table, rows as list, optional equationCriteria as any) as logical 

This determines whether any of the specified records appear as rows in the table.

I can use this function to see if any of my salespeople have contacted a company.

The M code I have used is:

= Table.ContainsAny([Contacts], {[Name="John"], [Name="Mary"], [Name= "Paul"], [Name="Newbie"]})

When I press OK, I will see which companies have had any contact with my employees:

Some good news, all the companies have been contacted, even if some companies have been contacted more than once!

Come back next time for more ways to use Power Query!

Newsletter