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!
