Power BI : Hiding tables, columns, and fields from Power Pivot
Last Updated :
13 Dec, 2023
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
Similar Reads
Table and Chart Combinations in Excel Power Pivot
For data exploration, visualization, and reporting, Power Pivot offers a variety of Power PivotTable and Power PivotChart combinations. A Power PivotChart is a PivotChart that was made using the Power Pivot window and is based on the Data Model. Despite sharing certain functionality with Excel Pivot
3 min read
How to Get Column from One Table to another in Power Bi
Real-world data is always messy. There will be always in need for adding new columns from one table to another because no table will have complete information. Adding a column allows for more efficient data analysis as well as the ability to add additional information to the table. Adding a column f
4 min read
How to Remove Old Row and Column Items from the Pivot Table in Excel?
Pivot table is one of the most efficient tools in excel for data analysis. If you are using pivot tables frequently, then you will find even after deleting the old data from the data source, it remains in the filter drop-down of the pivot table. We will learn, how to remove the old row and column it
4 min read
Power BI - How to Add Conditional Columns?
Creating insightful reports often requires data to be organized and categorized based on specific conditions. Conditional columns helps change or add values in a column based on logic you define. In this article, we’ll learn how to create conditional columns using Power Query Editor. Step 1: Open Po
2 min read
Remove Multiple Columns from data.table in R
In this article, we are going to see how to remove multiple columns from data.table in the R Programming language. Create data.table for demonstration: [GFGTABS] R # load the data.table package library("data.table") # create a data.table with 4 columns # they are id,name,age and address da
2 min read
Power BI - Format Line and Stacked Column Chart
A Line and Stacked Column chart is a combination of a line chart with a stacked column chart. We could add a secondary y-axis, with just 2 mouse clicks in Power BI. We have various options to format line and stacked column charts, we can change the value of the x-axis, y-axis, its title, etc. We can
11 min read
Hide Certain Columns in a Responsive Data Table Using DT Package in R
The DT package in R Shiny provides a powerful and interactive way to display data tables in web applications. One useful feature is the ability to hide certain columns based on user interactions or display requirements. In this guide, we’ll explore how to hide columns in a responsive data table usin
3 min read
Prepare Source Data for Pivot Tables In MS Excel
A pivot table is a tool for summarizing data that is derived from larger tables. A database, an Excel spreadsheet, or any other data that is or could be transformed into a table-like shape might be these larger tables. A pivot table's data summary may include sums, averages, or other statistics that
4 min read
Adding Columns in Power BI Desktop
Power BI is used for the analyses of all the business-related factors and for calculating different factors which are of prime importance in business running. The data can be stored in separate tables where many columns can be used to represent different data types. In this article, we will learn ho
6 min read
Power BI - How to edit in Power BI App?
Power BI is a data visualization application that lets you connect, transform and find insights into the most pressing matters of your business. It helps in sourcing your data and creating visual dashboards, KPIs, and reports by editing the data as per your concerns. You can collaborate, share and i
8 min read