Hide unwanted columns in Pivot Table with Power Pivot

Redundant or unwanted  columns can confuse the users of Pivot Table. Here is how you can remove unwanted columns from appearing in the Pivot Table field list.

Poster showing Excel logo Data Model menu showing Hide form Client tools option

The Need

This article assumes familiarity with Power Pivot usage. Read the Data Analytics article series to get familiar with PowerPivot. For this article please download this sample file.

You must have Office 2010 or above with PowerPivot add-in installed.

Open the sample file. Enable Connections if you are prompted for it.

Open the data model from Power Pivot – Data Model – Manage menu. There are two tables: Product Master and Data. Product master contains ID and name.

Product Master columns Product ID and Product Name

The Data table contains three columns: Product ID, Amount and Date. A relationship has been created between the Data and Product Master tables using the common column – Product ID.

Data Table showing Product Id, Amount and Date columns

Now when you create a Pivot Table, both tables and all columns are shown in the field list.

Pivot Table field list showing both tables and all the columns

Product ID is shown twice. However, we do not need to use Product ID at all in the Pivot Table. We will always want to use the Product Name. This is what I call confusion. We want to eliminate these redundant Product ID columns.

We cannot REMOVE these columns from the data itself – because these columns are required for maintaining the relationship. We just want to hide them from the Pivot Table in order to simplify things.

The solution is to use a nice little feature of Data Model editor (PowerPivot Data Model window).

The Solution: Hide unwanted columns from Client Tools

In the data model, right click on the Product Id column in the Data table and choose Hide from Client Tools. Repeat this for the Product Master table as well.

Hide unwanted columns

Now notice what happened in the Pivot Table field list. The unwanted columns are not visible at all. Only the columns that are relevant from business point of view are visible.

Pivot Table field list showing simplified field list

Similarly, Power View and Power Map will also show only these columns. Product ID columns will be invisible.

Scenarios where hiding columns is recommended

  1. Columns based upon which calculated columns are created where the original columns are not important for analysis
  2. Individual components of hierarchy are usually hidden because the hierarchy has all the fields in it. For example, if you created a hierarchy called Location which contains Country, State and City, the individual columns need not be shown at all.

This is NOT a substitute to importing only the required data

Remember that when you import data from any source, you must import only the columns you need for the analysis from a business point of view. This way you minimize the data size and increase performance. Hiding columns from client tools does not reduce the file size. The data is very much there, it is just hidden from Pivot Table, Power View and Power Map.

Power BI Desktop Designer

Please note that the Desktop Power BI Designer does not use Excel data model at all. It allows you to import data from various sources (including Excel) directly into the designer and creates its own data model independently. However, it does have a similar option which serves exactly the same purpose called: Hide from Report View.

Hide from Report View option in Power BI Desktop designer


Comments? Suggestions? Wish list?