Have you ever wondered how to hide columns in Power BI? Report optimisation is crucial for delivering high-quality, efficient, and reliable Power BI reports that meet the needs of your users, support accurate decision-making, and contribute to a positive overall data analytics environment. The volume of dataset is a crucial factor to alter the report performance.
An optimised report should have only necessary data and blank and irrelevant columns should be hidden or removed.
Removing or hiding irrelevant columns in Power BI contributes to a more efficient, effective, and user-friendly reporting experience. It streamlines your data model, enhances performance, and helps ensure the accuracy and security of your analysis.
In this article, we are going to analyse step by step process.
Hide Columns in Data view.
- Open your Power BI Desktop file. In the “Fields” pane on the right-hand side, you’ll see a list of tables and their respective columns.
- Locate the table containing the columns you want to hide. Right click on the column and click on “Hide in report view”.
- Repeat this process for any other columns you want to hide. Once you’ve hidden the desired columns, you’ll notice that they no longer appear in the data view.
- You can also click the “Data” tab at the top of the Power BI window to switch to the data view and see your hidden columns removed from the table. We have marked “ID” and “DayNumberofmonth” columns as hidden :
- Hiding columns in the data view does not delete the data or the column itself from your data model. It simply hides the column from view in the data table for a cleaner and more focused working environment. If you need to unhide a column later, you can follow the same steps by right click on the any of the columns and click on “unhide All”.
- Remember that hiding columns in the data view is a visual change and does not affect the underlying data model or any visualisations you create in your reports.
Hide Columns in Power Query
It is recommended to hide irrelevant or blank columns in power query for better performance. This will decrease the file size and will not load the columns in the data view on the next refresh.
- Open your Power BI Desktop file. Right click on the table and click on edit query.
- Navigate to the table and it will show all the columns available in the data set.
- Select the columns which are irrelevant or with blank values. Right click and click on remove columns. We have two columns in this example. If you have more columns to be removed, then go go for “Remove other columns” but make sure you have selected those columns which are not for deletion.
- Navigate to the applied steps and you will see an action added for column deletion.
- Click on close and apply to load the data into data view.
- You will see selected columns are not available in the data view.
If our data source allows query folding, then we can limit the load of these columns using query folding. It will further decrease the data size and will certainly improve performance. You can refer to our article on Query folding to understand the concept and its implementation in Power BI.
Conclusion
In this article, we learnt about the ways to hide or remove irrelevant or blank data columns in Power BI Removing unwanted columns in Power BI Desktop can lead to improved performance, enhanced security, simpler development, and a better overall user experience. It’s best practice to regularly review your data model and remove columns that are not necessary for your specific reporting needs.
Vidi-Corp is a Power BI Consultancy and we offer bespoke Power BI training for data-driven businesses. Contact us now to discuss how we can empower you and your team.
