Filters and Slicers

Filters and Slicers

Previous Session Summary

In the previous lesson, we covered how to enter the PowerQuery editor for the first time by highting some Purchase Order Data in our current workbook and then going to the Data tab and then going to From Table/Range.
 
We then looked at the different components of the PowerQuery editor being the Preview Screen, the Query Pane, the Tabs and Ribbon and the Query Settings Pane.

Lesson Synopsis

In this lesson, we look at the difference between Pivot Table filters and slicers. We’ve seen filters since the beginning of the course. These are ways of restricting the information in the connected Dataset, that the Pivot Table can see to summarise in the Pivot Table. We create these filters by adding fields from the connected dataset into the filters pane of the Field List.
 
Slicers do the same thing but as act as preset buttons which are available in the worksheet of the Pivot Table. They can be formatted as required and can also be set to control more than one Pivot Table. The purpose of slicers are to do the same thing as filters but are more intuitive for those who are less familiar with Pivot Tables.

Resource Listing

The Video

Play Video

Lesson Notes

Filters

Filters, filter data in pivot tables, so that the pivot table only ‘sees’ data in line with the filter settings that we as the user have set. As it can only ‘see’ certain data, it can only aggregate the information that it sees.

In previous lessons we filtered the pivot table to only include data for 2016 data only and then added in 2017 data later on, so let’s remind ourselves of how that filter setting works.

First, open the pivot table field list by left clicking on the pivot table to make it active and then right clicking and selecting “Show Field List”. Then, let’s say that we only want to look at the sales from a certain Country of the world and then from a certain Sales Segment.

Firstly, move the Country field to the Filters pane and then move the Segment field to the Filter pane as well. We’ve now got both of those options above the pivot table as drop down boxes and we can select the option that we’d like.

Let’s say we’d then like to look at Mid tier sales in New Zealand. I’d click on the drop down box for Country above the Pivot Table and then select New Zealand from the list. To select Mid Tier, click on the Segment drop down box and select Mid Tier from the list. The pivot table then shrinks to show all of the sales that have been made in New Zealand in the Mid Tier segment. You can put in as many filters as you have fields so don’t think you’re restricted to one or two.

Other Filters

When your Pivot Table is in a tabular form rather than a compact form, you can also see in the top left hand corner of the table a number of drop down arrows next to each of our Rows and Columns fields. These are also all filters. These can also be use to filter the data that the Pivot Table is ‘seeing’ to perform its aggregation calculations.

However, if you’re using a compact form, then the Pivot Table will only show a filter against one of the Fields in your Pivot Table; not all of them.

Slicers

The second type of filter is called a “Slicer”. These are essentially buttons that we can put into the report around the Pivot table for people who have not produced the report, who don’t perhaps create pivot tables and are less familiar with how they work. This type of person sometimes doesn’t know how the filter drop down options work but still wants to be able to filter the pivot table to only show certain information.

 The Slicers are simply buttons that allow the filtering process without the pivot tables filter dropdowns.  We can put slicers in instead of filters and then those people can use the pivot tables themselves with little guidance.

To insert a slicer, click on the Pivot Table and select the PivotTable Analyze tab. In the Filter section of the ribbon, then select Insert Slicer.

A menu then pops up showing us all of the fields in our dataset, from which the Pivot Table can be created.

Let’s imagine that we wanted to put in options for any reviewer to filter the data by the Country and the Segment, as we did earlier, as well as the year. I’ll select Insert slicer and then check the boxes for segment, country and year and then click “Ok” That then brings up three boxes with buttons labelling the possible options for each field. The slicers can then be easily resized.

Now any reviewer can simply click these buttons to review any combination of settings that they like.

To clear filter selections, just click the button in the top right hand corner of the slicers and the pivot table will then return to the initial Pivot Table settings.

...and another thing!

Filtering multiple Pivot Tables – In the future you may wish to filter two or more pivot tables at once, using the same filter. You can’t do this using standard Filters as these are attached to a single Pivot Table. However, you can do this using Slicers!

To do this, simply create your slicer and your two pivot tables. Once the slicer is created, right click the slicer  and then select