Have you ever thought about how to conceal columns in Power BI? Optimizing reports is vital for creating high-quality, efficient, and reliable Power BI reports that address user needs, assist in accurate decision-making, and foster a positive data analytics atmosphere. The size of the dataset significantly influences report performance.
An optimized report should present only essential data, with any blank or irrelevant columns being hidden or eliminated. Removing or concealing non-essential columns in Power BI enhances the reporting experience, making it more efficient, effective, and user-friendly. This process streamlines your data model, boosts performance, and helps maintain the accuracy and security of your analysis.
In this article, we will examine the steps involved in hiding columns.
Hide Columns in Data View
Start by opening your Power BI Desktop file. In the “Fields” panel on the right side, you’ll find a list of tables along with their corresponding columns.
Identify the table that contains the columns you wish to conceal. Right-click on the column and select “Hide in report view.”
Repeat this for any additional columns you want to hide. After hiding the chosen columns, you’ll observe that they no longer show up in the data view. Click the “Data” tab at the top of the Power BI window to switch to the data view and confirm the hidden columns are removed from the table. We have marked the “ID” and “DayNumberofmonth” columns as hidden:
Hiding columns in the data view does not delete the columns or data from your data model. It merely conceals the column from view in the data table, creating a cleaner and more focused working environment. If you need to reveal a column later, simply follow the same steps by right-clicking on any of the columns and selecting “unhide All.”
Keep in mind that concealing columns in the data view is a visual adjustment and does not alter the underlying data model or affect any visualizations you create in your reports.
Hide Columns in Power Query
For better performance, it's advisable to conceal irrelevant or blank columns in Power Query. This action will reduce the file size and prevent those columns from loading in the data view during the next refresh.
Open your Power BI Desktop file. Right-click on the table and choose to edit the query.
Navigate to the table, where you will see all columns available in the dataset.
Select the columns that are irrelevant or contain blank values. Right-click and choose to remove columns. In this example, we have two columns. If there are additional columns to be eliminated, opt for “Remove other columns,” ensuring you've selected the columns meant for deletion.
Look at the applied steps, where you will see an action registered for column deletion.
Click on close and apply to load the data into the data view.
You will find that the chosen columns are no longer displayed in the data view.
> If your data source enables query folding, you can limit the loading of these columns through query folding. This will further reduce the data size and undoubtedly enhance performance.
Conclusion
In this article, we explored various methods to hide or remove irrelevant or blank data columns in Power BI. Eliminating unneeded columns in Power BI Desktop can improve performance, enhance security, simplify development, and provide an overall better user experience. Regularly reviewing your data model and removing columns that aren't essential for your specific reporting objectives is a recommended best practice.
