Months ago, we got the integration between the Power BI and the Sharepoint lists, respectively, between Microsoft Lists. (Integration Setup in Power BI Admin Portal)
This integration adds a button to Sharepoint lists that allows you to use that lists as a data source from which a dataset is automatically created.
Integration button in Sharepoint Lists
This integration was gradually expanded because Power BI will try to create an automatic report from the given sheet in addition to the dataset. This is efficient when we need to reveal the basic information about the values in the sheet, or we need more time to create a report ourselves, and we need some overview that we can customize according to our wishes.
I used this Sharepoint List for the demonstration:
List with demo data
The following report was automatically generated from it:
Auto-generated report from demo data.
In the report, as I already mentioned, we can make adjustments. But, at the same time, you only need to select another field, and the existing generated report will be automatically expanded.
The official documentation HERE shows all the fundamental limits and descriptions of the behavior.
Limiting who can use this integration
Like many other settings in the Power BI Admin portal, this integration can be set so that everyone can use it or only limited security groups (or certain groups are not allowed to use it).
Power BI Admin Portal settings
The exact recommended setting will always depend on the company’s internal policies. If this integration is disabled, Power BI should not be displayed in the integrations at all for List. However, if someone tries to use that integration, for example, in the interim when it is being deactivated, they will be greeted with this message:
The error returned by Power BI after deactivating this integration.
In any case, unauthorized users will not be able to use the integration. But those who do can use it for every single sheet. Which also may not be desirable in any way. However, there are no settings for individual sheets in the Power BI Admin Portal. Can we restrict it like this? (Short Answer: YES, Longer Answer: YEEEEEEEEEES! (Citation of Will Thompson (@MI77@mastodon.online) (@Will_MI77) / Twitter at Power BI Next Steps))
Sheet-level integration settings
The idea for this solution comes from Michel Mendes. In one of his last posts, he talked about the fact that within the JSON definition of the layout, it is also possible to influence the buttons located above the List. Specifically, he showed it using the NEW and UPLOAD buttons as an example. Hiding the New and Upload buttons from a SharePoint document library using JSON list formatting - michelcarlo
This piqued my curiosity as to whether it would be possible to hide the integration button with a similar variant, either entirely or only its subsections (for example, make ONLY Power BI disappear).
As Michel mentioned, “commandBarProps” a beautiful definition of objects, can be modified within the upcoming JSON structure.
This led to the preparation of the first script. That makes the whole Integrate button disappear:
{
"$schema": "https://developer.microsoft.com/json-schemas/sp/v2/row-formatting.schema.json",
"commandBarProps": {
"commands": [
{
"key": "integrate",
"hide": true
}
]
}
}
How would you like to use such a code? First, it needs to be applied to a new or existing view. We can do this, for example, via All items (Or another name of the view that is set as a default view) -> Format Current view.
Formatting for view in Sharepoint list.
But this will natively show you the basic options for formatting or altering lines. But at the bottom of this window, you can find “Advanced mode” which is often coded in Microsoft technologies (for example, in Power Query).
Advanced mode to open JSON specification of the layout.
Here we can test the embedded code using Preview and see if it does exactly what you would expect from it. In our case, it makes the Integrate button disappear.
Preview of inserted JSON.
But then came a minor epiphany. If we let the entire button be hidden, then even Power Apps or Power Automate cannot be invoked in this way. This would only make the disappearing scope even narrower:
{
"$schema": "https://developer.microsoft.com/json-schemas/sp/v2/row-formatting.schema.json",
"commandBarProps": {
"commands": [
{
"key": "powerBI",
"hide": true
}
]
}
}
The difference between these scripts is only in the “key” attribute, which determines the object to which we will refer. Since we only want to make it disappear, the attribute “hide” with the value remains.
Sharepoint List - Integrate button without Power BI
So the Integrate button is shown but without Power BI. Hurrah!
Summary
If you save such a setting within the “All items” view or the user’s native view, you will be sure that the user will not click on this option at first. So, YES, it is NOT a SECURITY option. It is an OPTICAL variant. Therefore, a user authorized to use the integration and can create his views within the sheet may be able to reaccess this integration, especially when these layouts are kept from views.
Anyway, it’s great that there are options to keep some of this thread hidden from regular users if needed. After all… If something can only be seen in certain places or specific situations, it will appear to the average user that it is “useful” only in specific cases.
