Power Query: Are Any of Them on the Table?
26 June 2019
Welcome to our Power Query blog. Today, I revisit the data I looked at last week to extend one of my solutions.
Last week, I checked if a company had been contacted by any of my salespeople, using 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 used this function to see if any of my salespeople had contacted a company.
![](http://sumproduct-4634.kxcdn.com/img/containers/main/blog-pictures/2019/power-query/134/image1.png/e774d10cbbb9450fc45efbe51abdf434.jpg)
The M code I have used is:
= Table.ContainsAny([Contacts], {[Name="John"], [Name="Mary"], [Name= "Paul"], [Name="Newbie"]})
When I pressed OK, I saw which companies had any contact with my employees:
![](http://sumproduct-4634.kxcdn.com/img/containers/main/blog-pictures/2019/power-query/134/image2.png/f32e5a15e2cf9c3e4d2d058458ce054d.jpg)
This showed me that all the companies have been contacted, even if some companies had been contacted more than once. However, whilst it’s feasible to use individual names for a small group of salespeople, if I had a larger imaginary company, then I would want a way of doing this without using specific names.
![](http://sumproduct-4634.kxcdn.com/img/containers/main/blog-pictures/2019/power-query/134/image3.png/f1140ff857fc3b6f5f97a6a24f4a6fc7.jpg)
There are a couple of ways I could do this for my data.
I go back to the query I created in Power Query: Is it on the Table? which checked if any of my named people had contacted each company.
![](http://sumproduct-4634.kxcdn.com/img/containers/main/blog-pictures/2019/power-query/134/image2.png/f32e5a15e2cf9c3e4d2d058458ce054d.jpg)
One way I can approach this is to amend my M code check a different column. I started with the M
= Table.ContainsAny(Contacts, {[Name="John"], [Name="Mary"], [Name= "Paul"], [Name="Newbie"]})
I can change this to:
= Table.ContainsAny(Contacts, {Mail = “Yes”})
![](http://sumproduct-4634.kxcdn.com/img/containers/main/blog-pictures/2019/power-query/134/image4.png/72aa864d2854c6fefb1083fba0ab5792.jpg)
I can see that all my companies have been contacted.
This does leave the question of the best way to see if the companies were contacted by a specified list of people, without actually listing them. If I try and use Table.ContainsAny(), I would need to generate a list of records, which is tricky to format. An easier way is to create a table of salespeople, and then use a ‘Salespeople’ query to link to my data and get the answer that way.
![](http://sumproduct-4634.kxcdn.com/img/containers/main/blog-pictures/2019/power-query/134/image5.png/36776d1da4d05b45bb5a5d09375f407c.jpg)
I have created a table of salespeople (and have left out Newbie) with the goal of checking if the companies have been contacted by the people in this table.
![](http://sumproduct-4634.kxcdn.com/img/containers/main/blog-pictures/2019/power-query/134/image6.png/23912d3b1671861e02bebcd5183f1607.jpg)
I merge my queries, taking the defaults and matching on Name.
![](http://sumproduct-4634.kxcdn.com/img/containers/main/blog-pictures/2019/power-query/134/image7.png/6f49c288a0d88a66b427eaf4ece923d6.jpg)
Then, I expand my data.
![](http://sumproduct-4634.kxcdn.com/img/containers/main/blog-pictures/2019/power-query/134/image8.png/b9ee28d90e6b5bc92ea4aeafdad51628.jpg)
I can now create a custom column which will indicate if the company has been contacted by one of my main salespeople.
![](http://sumproduct-4634.kxcdn.com/img/containers/main/blog-pictures/2019/power-query/134/image9.png/0485ccbc83bdeec1d741bad442a1ea5f.jpg)
I can now rename my column and remove the temporary column.
![](http://sumproduct-4634.kxcdn.com/img/containers/main/blog-pictures/2019/power-query/134/image10.png/daf8c4f0259ce428269c0d3d4badd32b.jpg)
I can now see who has been contacted by my main team.
Come back next time for more ways to use Power Query!