Find V1 Workspace Owners

As most readers of this blog likely know, there are two very different kinds of workspaces in Power BI – V1 or ‘classic’ workspaces which are tied to Office 365 groups and V2 or ‘modern’ workspaces which are not. V2 workspaces have many advantages beyond their independence from Office 365 which you can read about elsewhere but for a bit of context you can read the GA announcement of V2 workspaces from back in April.

Since upgrading to V2 workspaces has been a manual process thus far, most Power BI tenants contain a mix of V1 and V2 workspaces. You may also have read the recent announcement of a new feature in the Power BI service available to workspace admins to upgrade their V1 workspaces. This blog post is all about identifying these V1 workspaces and their admins.

Should you upgrade now? No

The new workspace upgrade feature is in public preview for a reason. Like other preview features in Power BI, you should only use it for test/evaluation purposes, not for any production workloads. So if you’re someone who administers Power BI, you might want to contact your V1 workspace admins and advise them to ignore this preview feature and thus hold off on upgrading for now.

Yes, I know they’re providing roll back option for 30 days. You can trust this limited restore preview option at your risk.

Who are my V1 workspace admins?

It’s easy to find your V1 workspaces with the Power BI Management PowerShell module and specifically the Get-PowerBIWorkspace command. However, it’s not so easy to retrieve the users in these workspaces. As mentioned in a recent post, the relatively new Expand parameter of the Get-GroupsAsAdmin REST API endpoint, which includes artifacts in addition to users, is exclusive to V2 workspaces.

Solution Summary

For an ad hoc or one-time approach, you could do the following:

1. In a PowerShell script, retrieve the active V1 workspaces from Power BI
2. In a PowerShell script, retrieve the groups and group owners from Azure Active Directory
3. Perform a join between the Power BI workspaces and the Azure AD groups

In this example, the data from steps 1-2 are written to CSV files and then joined in step 3 via Power Query (M) inside a Power BI dataset (.pbix):

You can download the PowerShell script used in this example from the Insight Quest GitHub repository.

Note: If you’ve invested in an actual Power BI Admin database (including its nightly ETL process) containing both Power BI and Azure AD metadata as described in previous posts, then you could likely solve this problem quickly via SQL queries or in Power BI.

Solution Details

The PowerShell script is straight forward (with comments) and uses both the Power BI Management module and the AzureAD module. Since this is an ad hoc scenario, there’s no unattended authentication mechanism like a service principal or certificate thumbprint. You’ll of course need to modify the file paths used in the Csv-Export command.

The workspaces are filtered for active and V1 (‘group’) workspaces only. The Azure Active Directory (AAD) portion of the script first retrieves only the AAD ‘groups’ (not security groups) and then loops over each group to get its group owners. Of course this code takes some time with many groups – about 30 minutes for 2,300+ groups in my example.

At this point, Power BI Desktop simply needs to connect to the two CSV files and perform an inner join between the Object ID of the Azure AD group table and the workspace ID column of the V1 workspace table:

After the join you’d then just expand/expose the columns of the joined table (via outward facing column header dropdown) such that you have four columns from Azure AD and four columns from the Power BI workspace.

I’d recommend separate queries for each CSV file and disabling the load of these source (‘staging’) queries to the model layer. You’d then ‘Merge Queries as New’ to create a separate query from the sources queries (1 for each CSV file):

For each CSV table query you’ll need to skip the first row and then promote the next row as headers – both transformations are easily available in the Power Query Editor. Like many other simple and common transformation scenarios in Power Query, there’s not any custom M development required to produce the target table with 8 columns.

With the merged table loaded to the Power BI modeling layer, even if you’re fairly new to Power BI development, you can build some visuals and metrics to answer basic questions such as “Which users/owners are associated with the highest volume of V1 workspaces? or “Who owns the premium hosted V1 workspaces?” You could use the Office 365 Admin Center to look up the owners for a particular Office 365 group to confirm the data retrieved from the script and joined is indeed accurate.

Wrapping Up

So there you have it – with just a little PowerShell and a few clicks in the Power Query Editor of Power BI Desktop you have the essential data to better plan out your upgrade of V1 workspaces. As is usually the case, there are other approaches such as performing the join within the PowerShell script itself but since Power BI is so common for data analysis and visualization it seems to make sense to handle the join in Power BI as well.

If you think posts like this are useful or interesting you can click ‘Follow’ to receive email notification of future posts.