Redundant or unwanted columns can confuse the users of Pivot Table. Here is how you can hide unwanted columns from appearing in the Pivot Table field list. This setting is available in the Power Pivot Data Model.
Contents
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.
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.
Now when you create a Pivot Table, both tables and all columns are shown in the field list.
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.
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.
Similarly, Power View and Power Map will also show only these columns. Product ID columns will be invisible.
Scenarios where hiding columns is recommended
- Columns based upon which calculated columns are created where the original columns are not important for analysis
- 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.
***
