A robust data modeling tool in Power BI called Power Pivot enables users to build data models by fusing several data sources, building relationships, and including computed columns and metrics. But occasionally you might want to exclude some tables, columns, or fields from the Power Pivot view in Power BI. This could be done to clean up the area, safeguard private information, or just for better data organization. The idea of hiding tables, columns, and fields from Power Pivot in Power BI will be discussed in this post, along with step-by-step instructions and screenshots.
Hiding Tables, columns, and fields in Power BI
You can use the steps below to conceal tables, columns, and fields from Power Pivot in Power BI:
Step 1: Launch Power BI Desktop and select the “Data” tab in the left-hand pane to get the Data view.
Step 2: A list of all the tables that make up your data model can be found in the Data view. To conceal a table, perform a right-click and choose “Hide” from the context menu.
Step 3: The table is now hidden from the Data view and is not present in the Power Pivot view anymore.
Step 4: Go to the Home tab and select the “Show hidden” button under the Table Tools section if you wish to reveal a table that has been hidden. By selecting “Unhide” from the context menu when you right-click on a hidden table, you may make it visible once more. This will display all of the hidden tables.
Step 5: In a similar manner, you can right-click a column or field in the Data view and choose “Hide” from the context menu to make it invisible. Both the Data view and the Power Pivot view will have the column or field hidden.
Alternative Methods for Hiding Tables, Columns, and Fields
There are a couple of additional methods for hiding Power Pivot tables, columns, and fields in Power BI:
Using Power Query
It is one way to shape and transform data before it is loaded into the data model. Power Query is another data modeling tool in Power BI. Before your data is entered into Power Pivot, you can use Power Query to filter out or eliminate undesirable tables, columns, or fields. Power Query transformations like Remove Columns, Filter Rows, and Select Columns can be used for this.
In order to hide columns from your data using Power Query, consider the following example:
Step 1: Open Power Query Editor by selecting “Transform Data” on the Home tab of Power BI Desktop.
Step 2: Choose the table that includes the column you wish to hide in Power Query Editor.
Step 3: From the context menu, right-click on the column header and choose “Remove”.
Step 4: Press “Close & Apply” to save the modifications and shut down Power Query Editor. Both the Data view and the Power Pivot view will now have the column hidden.
Hide Rows and Columns using DAX
Using DAX (Data Analysis Expressions) calculated columns is another method for hiding columns from Power Pivot. You can use the HIDE() function in DAX to conceal a column by creating a calculated column in Power Pivot that references the column you wish to conceal.
Hide rows and columns in Power BI:
Step 1: Click on “Manage Roles” in the security section of the Home tab.
Step 2: Click on “Create” to create new DAX expressions.
Step 3: Give a name to the Role and select the sheet in which you can write DAX expressions.
Step 4: Write DAX for hiding i.e. [Age]<>24
Step 4: Click on “Save”
Step 5: Now go to “View as” and select the role i.e. “Hide” and then click on “Ok”, the modified data will be obtained.
Unhide rows and columns in Power BI
All steps are similar to the hidden just we have a different DAX expression to show specific columns
Expression to show is for example : [Age]=24