We are excited to announce the recent release of support for $expand in the GetGroupsAsAdmin API! As a Power BI service admin if you need to list all workspaces in your tenant, including their users, reports, dashboards, and datasets, $expand helps you do this quickly and efficiently.
Support for $expand in the GetGroupsAsAdmin API enables you to retrieve the details of the navigation properties for users, reports, dashboards, and datasets directly in the workspace properties. You no longer need to loop through each workspace and call 4 separate APIs. With $expand, you can accomplish that work with a single API call. This makes your solutions dramatically simpler, more intuitive, faster to develop, and easier to maintain.
Using the $expand query option is very straight forward. Here’s the API call to get the first 5000 workspaces in your tenant.
https://api.powerbi.com/v1.0/myorg/admin/Groups?$top=5000
This just returns a list of workspaces names, GUIDs, and other metadata. Now let’s see how easy it is to get all the users, reports, dashboards, and datasets in these workspaces.
To do this just append $expand and specify which objects you’d like to expand as shown below.
https://api.powerbi.com/v1.0/myorg/admin/Groups?$top=5000&$expand=users,reports,dashboards,datasets
The following listing shows a sample result returned for a single workspace:
"value": [
{
"id": "183dcf10-47b8-48c4-84aa-f0bf9d5f8fcf",
"isReadOnly": false,
"isOnDedicatedCapacity": false,
"name": "Sample Group 2",
"description": "Deleted sample group",
"type": "Workspace",
"state": "Deleted",
"users": [
{
"emailAddress": "john@contoso.com",
"groupUserAccessRight": "Admin"
}
],
"reports": [
{
"datasetId": "cfafbeb1-8037-4d0c-896e-a46fb27ff229",
"id": "5b218778-e7a5-4d73-8187-f10824047715",
"name": "SalesMarketing"
}
],
"dashboards": [
{
"id": "69ffaa6c-b36d-4d01-96f5-1ed67c64d4af",
"displayName": "SalesMarketing",
"isReadOnly": false
}
],
"datasets": [
{
"id": "cfafbeb1-8037-4d0c-896e-a46fb27ff229",
"name": "SalesMarketing",
"addRowsAPIEnabled": false,
"configuredBy": "john@contoso.com",
"isRefreshable": true,
"isEffectiveIdentityRequired": false,
"isEffectiveIdentityRolesRequired": false,
"isOnPremGatewayRequired": false
}
]
}
] If you have more the 5,000 workspaces, then use the $skip parameter to page through the remaining workspaces with a batch size of 5,000.
If your Power BI tenant has 50,000 workspaces, you can retrieve all that information with just 10 calls. Compare that to a loop through 50,000 individual workspaces with 3 separate calls to get the reports, dashboards, and datasets – a dramatically easier solution.
The workspace PowerShell cmdlets don’t support $expand yet. However, you can use the Invoke-PowerBIRestMethod cmdlet to make the calls. Just keep in mind that you must escape special characters in the URL. For example, %24 represents the dollar sign in $expand. Below is an example you can try.
Login-PowerBI $result = Invoke-PowerBIRestMethod -Url "https://api.powerbi.com/v1.0/myorg/admin/Groups?%24top=1&%24expand=reports,dashboards,datasets" -Method Get $workspaceContents = $result | ConvertFrom-Json $firstWorkspace = $workspaceContents.value[0] Write-Host "" Write-Host "Reports in first workspace:" Write-Host "---------------------------" $firstWorkspace.reports | Format-List Write-Host "---------------------------" Write-Host "" Write-Host "Dashboards in first workspace:" Write-Host "---------------------------" $firstWorkspace.dashboards | Format-List Write-Host "---------------------------" Write-Host "" Write-Host "Datasets in first workspace:" Write-Host "---------------------------" $firstWorkspace.datasets | Format-List Write-Host "---------------------------"
Here is the output:
To get started quickly, you can try the $expand query option in the Web browser. Just go to the Admin – Groups GetGroupsAsAdmin page in the Power BI REST API Reference. There you can click on Try It, sign in, and specify the $top and $expand parameters as discussed earlier. Optionally, you can also define the $skip parameter. Just make sure you click on the Plus (+) sign to apply the additional parameters to the URL. As a bonus, you’ll notice the URL displayed under Request Preview includes all parameters nicely escaped, which makes it easy to copy and paste the URL into a Invoke-PowerBIRestMethod call. The following screenshot shows you the Try It feature in action.
And that’s it for a brief introduction of the recently added support for the $expand query option in the GetGroupsAsAdmin API!
To get more information and keep up to date for API improvements, occasionally check the .NET SDK for Power BI and the Open Source repository for Power BI cmdlets on GitHub.