Hide tables in Power BI
It is possible to hide tables in Power BI by using the “Hide in report view” option, however, if a user downloads the PBIX file this is still possible to view hidden tables or to unhide tables and this is also true for the reports with a live connection to SSAS or PBI dataset.
In this post, I’m going to show another way to hide tables in Power BI which prevents users or anyone to view the hidden tables.
Table of Contents
Hide tables in Power BI
To hide tables in Power BI we can either do it from the Model view or directly from the Report view.
Once done we can see the hidden icon enabled on the table “v_dimDate”
And once we open the Report view we can no longer see the hidden table, so far so good?
Well until your users get access to the PBIX file and discover the option to unhide tables or to view hidden tables.
Unhide all and View hidden
As mentioned at the beginning of this post once we get access to the PBIX file of a report or even if we have a live connection to a tabular model or a PBI dataset it is possible to view the hidden objects.
As we can see the table “v_dimDate” is now visible even though we’ve hidden the table
Prevent users from viewing hidden tables using Private
In order to fully prevent users from seeing hidden tables, we need to install tabular editor (version 2 or 3) and enable the external tools in Power BI.
Once the model is opened via tabular editor we have access to the Tabular Object Model (TOM) properties and we can modify them. The property that we need to change is “Private” once we set private to True the table becomes hidden and can no longer be seen in Power BI even when we enable “ViewHidden” or “Unhide all”.
The only drawback is that once the Private property is set to True Power BI will think that this table does not exist and thus the IntelliSense will not work anymore. As Power BI no longer recognise this table it will also highlight in red this table in any formula but we can ignore it as the formula will still work.
As we can see the “v_dimDate” is no longer visible even after we enable the “View hidden” option and even though Power BI does not recognise this table we can still reference it in any DAX formulas.
If the developers are developing directly into Power BI it is probably a good idea to temporary set Private to False while developing and set it back to True before publishing or sharing the file.
Conclusion
This option can be very useful when it comes to hiding the tables used to configure Row Level Security or any other internal tables that we don’t want users to see.
Of course, this technique does not replace the Object Level Security and should not be used for such a purpose and as a best practice, I’d always recommend not to give access to the underlying model of a report to the users and always use the Live connection whenever it’s possible.
Finally, to learn more about the Tabular Model I’d highly recommend to take the “mastering-tabular” course from SQLBI.
7 thoughts on “Hide tables in Power BI”
Hello,
I thought this was a great post, we often have a need to hide objects that we build for a central model (bridge tables, aggregation tables etc).
I cannot find this property in m Tabular Editor version 2.16.5
Under the Options category, I only see
Data Category
Default Detail Rows Expression
Lineage Tag
Is there another setting to enable I missed?
Hi Cody,
Yes bridge tables are also great uses cases I did not even think about them while writing this post thanks for the suggestion!
Regarding the option not being available you need to enable the feature “Allow unsupported Power BI features” in Tabular Editor by clicking onto File–>Preferences–>Features (I should have put that in my post as well)
Great post, Ben.
Is it possible to set a column to private?
Hi Rami,
Thanks as far as I know it is not possible and I’m not sure if this will change in the future.
Thanks for this great post. I was honestly scratching my head around how to hide tables for someone who knows powerbi and has contributor access in powerbi services (who can download the file and look at the restricted data).
However, the question is, what if the user knows tabular editor as well? Can he/she be able to turn the “Private” Property value to False and use the restricted information? Kindly respond as the answer to this question is going to solve some big business issues we have with powerbi.
Hi Latha,
1. The best practice is not to grant a contributor access to the dataset but rather “viewer” with build permission.
2. Then you can have a separate workspace for the reports which will all have a direct connection to the datasets.
3. You can grant contributor role on the report workspace so even if they can download the report they cannot change the underlying model if they try they will get a message unauthorised access.
4. My above statement is partly true since there’s a still way to bypass it by creating a composite model and then access the underlying model but I think Microsoft is working on it and should soon release a fix.
5. Private mode should by no means be used as security you should instead rely on OLS (object-level security) to apply real object security in your model.
I use the private mode only for technical tables that do not hold any sensitive data.
Hope that helps.