Please note javascript is required for full website functionality.
MVP

Blog

Power Query: Selective Staffing Part 5

24 November 2021

Welcome to our Power Query blog.  This week, I revisit my second inclusion example to show a solution that excludes data.

 

I have looked at two examples over the last few weeks where I included data based on a list of values.   Last week, I revisited my first example to see how I would have excluded the data instead.  This time I will revisit my second example:

In Power Query: Selective Staffing Part 3 , I had a table of quote data for each of my salespeople, and a list of salespeople that I wished to view quote details for:

I used M List() functionality, which I will revisit shortly, to achieve the result:

I am considering the situation where I want to exclude salespeople instead.  The new Excel Tables are Sales_Quotes_Exclude and Quote_Selection_Exclude:

I need to create a List Query which will contain the excluded salespeople.  Since the column name has changed, the quickest way to do this is by extracting the data from the Excel Table Quote_Selection_Exclude using ‘From Table/Range’ from the ‘Get & Transform’ section of the Data tab:

I then choose to ‘Convert to List’ from the Transform tab:

The M code I used for the last example can be viewed in the Advanced Editor for the query Staff_Quotes.

I create a duplicate of Staff_Quotes, which I will call Staff_Quotes Exclude.

I need to change the Source step to point at the new Excel Table Staff_Quotes_Exclude instead of Staff_Quotes.  I change the M code from this:

= Excel.CurrentWorkbook(){[Name="Staff_Quotes"]}[Content]

to this:

= Excel.CurrentWorkbook(){[Name="Staff_Quotes_Exclude"]}[Content]

Having changed the code, I look at it further in the Advanced Editor:

let

    Source = Excel.CurrentWorkbook(){[Name="Staff_Quotes_Exclude"]}[Content],

    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Salesperson ", type text}, {"Quote Number", Int64.Type}, {"Success Rate", Percentage.Type}, {"Profit", type number}}),

    // Only keep rows if the name is on the Quote_Selection list

    #"Keep Included Rows" = Table.SelectRows(#"Changed Type", each List.ContainsAny(Record.ToList(_), Quote_Selection))

in

    #"Keep Included Rows"

I need to change the line

    #"Keep Included Rows" = Table.SelectRows(#"Changed Type", each List.ContainsAny(Record.ToList(_), Quote_Selection))

to reverse the logic and use Quote_Selection_Exclude instead of Quote_Selection.  I also rename the line to reflect its new purpose:

    #"Remove Excluded Rows" = Table.SelectRows(#"Changed Type", each not List.ContainsAny(Record.ToList(_), Quote_Selection_Exclude))

This means that the final ‘in’ step should also refer to the new name for the step

in

    #"Remove Excluded Rows"

I should also update the comments to reflect the new functionality:

   // now exclude anything that matches the list

When I click Done, the lines that are not excluded are shown:

The information for the ‘Remove Excluded Rows’ step reflects the current functionality:

There is often more than one method to achieve the same results, and I will look at another approach I could have used for this example, which I will show next week.

 

Come back next time for more ways to use Power Query

 

Newsletter