Power Pivot reporting properties: Hiding tables, columns, and fields from Power Pivot and Power View

Hiding a table, column, or field removes it from view – it doesn’t delete it. The table, column, or field is still part of the data model. In Power Pivot you can either select to show hidden or not. In Power View, there is no choice; if the item is marked (in Power Pivot) as hidden, you won’t be able to see it in Power View.

By hiding unnecessary tables, columns, fields, and measures, you’ll make it easier for coworkers to create reports and also easier for others to understand and use your shared reports.

1. Open Power PivotPower Pivot > Manage.

2. Select the Diagram View: Home > View > Diagram View. (You could also use Data View.)

3. If you have Show Hidden turned on, you’ll be able to see the previously-hidden tables and columns. They’ll be lighter than the unhidden tables and columns. If you have Show Hidden turned off, you will not be able to see the previously-hidden items.

   

4. Right-click the table or column you’d like to hide and select Hide from Client Tools.

   

   In Power Pivot, hiding a column does not remove it from the model. A hidden column can still be used as a sort column in a table. It can also be referenced in formulas that define calculated columns or calculated fields.

   Note: The ability to hide columns is not intended to be used for data security, only to simplify and shorten the list of columns visible to reports that use them.

5. Back in Power View, in your Fields list, the hidden table and fields are not visible.

   

   Note: When you hide or unhide in Power Pivot, you are making changes to the underlying data model. So when you return to Power View, you’ll be prompted to refresh the data in your workbook.

6. To Unhide tables and fields, return to Power Pivot: Power Pivot > Manage, right-click the item to unhide, and select Unhide from Client Tools.

   

7. Back in Power View, in your Fields list, you can see the fields that were Unhidden.

   

8. To hide a table, column, or field in Data View, right-click and select Hide from Client Tools.

   

   Note: If you accidentally hide a table or column that you’ve already used in an active report, don’t worry. The hidden item will no longer show up in the Fields list, however the report is unaffected and can be updated and shared. You won’t be able to use the now-hidden item when making new updates to the report layout and if you delete the visualization that uses the hidden item, you won’t be able to recreate that visualization because the column, field, or table is no longer available in the field list.