Power Query: Selective Staffing Part 4

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:

I needed to produce a version of this table only including the available salespeople:

I created a new table which showed only the included salespeople for each area:

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.

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.

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:

Currently, this is extracting data from the Excel table Staffing:

I can change this to Staffing_Exclude, to point at the new table.

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.

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:

The M code is:

Selection is the included staff table; I need to change this to use Selection_Exclude.

I also need to reverse the logic, as I need to include data which is not on the list.

I need to make these changes to all the ‘Custom Column’ steps.

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.

When I ‘Close & Load’ the data, I have only the salespeople that are not excluded:

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!