Power Query: Selective Staffing Part 4
17 November 2021
Welcome to our Power Query blog. This week, I revisit my first inclusion examples to find solutions that involve exclusion.
I have looked at two examples over the last few weeks where I included data based upon a list of values. I will look at both examples to see how I would have excluded the data instead.
The first example was in Selective Staffing Part 1 where I had allocated salespeople:
![](http://sumproduct-4634.kxcdn.com/img/containers/main/blog-pictures/2021/power-query/259/image1.png/e774d10cbbb9450fc45efbe51abdf434.jpg)
I needed to produce a version of this table only including the available salespeople:
![](http://sumproduct-4634.kxcdn.com/img/containers/main/blog-pictures/2021/power-query/259/image2.png/f32e5a15e2cf9c3e4d2d058458ce054d.jpg)
I created a new table which showed only the included salespeople for each area:
![](http://sumproduct-4634.kxcdn.com/img/containers/main/blog-pictures/2021/power-query/259/image3.png/f1140ff857fc3b6f5f97a6a24f4a6fc7.jpg)
However, I could have the situation where some of the salespeople have been booked for a conference, and I need to exclude them from my allocations.
![](http://sumproduct-4634.kxcdn.com/img/containers/main/blog-pictures/2021/power-query/259/image4.png/72aa864d2854c6fefb1083fba0ab5792.jpg)
The Excel Tables for this example are Staffing_Exclude and Selection_Exclude. I create a new query Selection Exclude which is similar to the query Selection I used in Selective Staffing Part 2. Selection extracted the included staff table Selection and converted it to a list. This query extracts Selection_Exclude.
![](http://sumproduct-4634.kxcdn.com/img/containers/main/blog-pictures/2021/power-query/259/image5.png/36776d1da4d05b45bb5a5d09375f407c.jpg)
You will recall that there were two main issues to solve when solving the inclusion problem. I needed to work out which values to keep and then remove spaces in the table. Only the first issue is affected by excluding instead of including, so I will make a duplicate of the query I finished in Selective Staffing Part 2, which is called Staffing. The first step I need to change is the Source step:
![](http://sumproduct-4634.kxcdn.com/img/containers/main/blog-pictures/2021/power-query/259/image6.png/23912d3b1671861e02bebcd5183f1607.jpg)
Currently, this is extracting data from the Excel table Staffing:
= Excel.CurrentWorkbook(){[Name="Staffing"]}[Content]
I can change this to Staffing_Exclude, to point at the new table.
= Excel.CurrentWorkbook(){[Name="Staffing_Exclude"]}[Content]
To save this change, I press ENTER or use the tick next to the formula bar.
Having changed the Source, I need to change any steps that selected data based upon the included list.
![](http://sumproduct-4634.kxcdn.com/img/containers/main/blog-pictures/2021/power-query/259/image7.png/6f49c288a0d88a66b427eaf4ece923d6.jpg)
The only steps that will be affected are the ‘Added Custom’ steps, as the remainder of the query is dealing with blank values.
If I look at the first ‘Added Custom’ step using the gear icon, I can see the M code I used:
![](http://sumproduct-4634.kxcdn.com/img/containers/main/blog-pictures/2021/power-query/259/image8.png/b9ee28d90e6b5bc92ea4aeafdad51628.jpg)
The M code is:
= if List.ContainsAny(Selection, {[#"Marketing "]}) then [#"Marketing "] else null
Selection is the included staff table; I need to change this to use Selection_Exclude.
= if List.ContainsAny(Selection_Exclude, {[#"Marketing "]}) then [#"Marketing "] else null
I also need to reverse the logic, as I need to include data which is not on the list.
= if List.ContainsAny(Selection_Exclude, {[#"Marketing "]}) then null else [#"Marketing "]
I need to make these changes to all the ‘Custom Column’ steps.
![](http://sumproduct-4634.kxcdn.com/img/containers/main/blog-pictures/2021/power-query/259/image9.png/0485ccbc83bdeec1d741bad442a1ea5f.jpg)
The salespeople in the excluded list have now been removed from the table, and the remaining steps will reorganise the data to remove the blank values. I rename the query Staffing Exclude.
![](http://sumproduct-4634.kxcdn.com/img/containers/main/blog-pictures/2021/power-query/259/image10.png/daf8c4f0259ce428269c0d3d4badd32b.jpg)
When I ‘Close & Load’ the data, I have only the salespeople that are not excluded:
![](http://sumproduct-4634.kxcdn.com/img/containers/main/blog-pictures/2021/power-query/259/image11.png/22c6daeb82d7d69ac88f878227e04b28.jpg)
Next time I will look at how to exclude data in my second example…
Come back next time for more ways to use Power Query!