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.
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:
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.
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.
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”})
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.
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.
I merge my queries, taking the defaults and matching on Name.
Then, I expand my data.
I can now create a custom column which will indicate if the company has been contacted by one of my main salespeople.
I can now rename my column and remove the temporary column.
I can now see who has been contacted by my main team.
Come back next time for more ways to use Power Query!
