If you are following the Power BI blog on a regular basis, you probably have noticed the Power BI APIs and cmdlets announcement for administrators, which introduced a set of APIs and cmdlets to work with workspaces, dashboards, reports, datasets, and so forth in Power BI. But there is much more to this than could be covered in a brief announcement. For starters, the management cmdlets are not just for administrators; they are also for Power BI users and developers. This article takes a closer look to show you how to take advantage of these cmdlets provided your profile fits any of the following target groups:
- Power BI administrators, who must work with resources at the Power BI tenant level, such as to enumerate workspaces, dashboards, reports, and datasets, recover orphaned workspaces, or discover the data sources used in the various datasets across an entire organization.
- Power BI users, who want to manage the resources they own in Power BI, such as to create a backup of all published reports across multiple workspaces.
- Power BI developers, who need to have a quick and easy way to prototype their solutions, such as by trying out custom REST API calls in the PowerShell console without having to resort to Web-based prototyping tools and copying access tokens.
- Power BI community members, who wish to contribute to the benefit of others, such as by joining the PowerShell community for Microsoft Power BI and helping build even more cmdlets.
Let’s cover these points in reverse order beginning with the community aspect. Power BI enjoys a vibrant community committed to helping each other getting the most out of Power BI. Community members frequently trailblaze through uncharted territory with solutions and tools that complement what’s available from Microsoft. PowerShell support for Power BI is a good example. Search for “power bi powershell” and you can find plenty of articles showing you how to use the Power BI APIs from the PowerShell console. You can even find a Microsoft.PowerBI.PowerShell module in the PowerShell Gallery and a collection of cmdlets for working with the PowerBI APIs on GitHub (https://github.com/DevScope/powerbi-powershell-modules). This is great, yet it creates a slight dilemma because cmdlets from different sources don’t work well together and they duplicate functionality while leaving gaps in other areas. It is much better to join forces, coordinate the work, merge what already exists, and deliver the richest possible collection of cmdlets consolidated in a single repository.
You can find this Microsoft-backed GitHub repo at https://github.com/Microsoft/powerbi-powershell. If you are interested in helping build cmdlets for Power BI, check out Contribute Code to PowerShell Cmdlets for Power BI and come on board as a project member. Or simply report any issues you encounter at https://github.com/Microsoft/powerbi-powershell/issues. All help is greatly appreciated.
One advantage of the Power BI management cmdlets is that they are available as a module from the PowerShell Gallery. Simply install them by using the command Install-Module -Name MicrosoftPowerBIMgmt -Scope CurrentUser. Another is that the management cmdlets do not require a separate app registration in Azure. So, if you are a Power BI app developer, it’s very straightforward and quick to prototype solutions that make Power BI Rest API calls. Just log in to Power BI by using the Login-PowerBI cmdlet. It supports user accounts, service principals, and application credentials (key or certificate). Then fire away by using one of the pre-built cmdlets or use the Invoke-PowerBIRestMethod cmdlet.
Invoke-PowerBIRestMethod is the Swiss army knife of Power BI cmdlets. You can use it to construct arbitrary REST API calls against Power BI. So, if you find that you are missing a specific management cmdlet, you can still get the job done if there is an appropriate REST API endpoint for you to call. For all the details about available REST API endpoints, see the Power BI REST API reference.
For working with workspaces, dashboards, reports, datasets, data sources, and imports, you don’t need to go to the Invoke-PowerBIRestMethod cmdlet. Instead, use the more specialized cmdlets Get-PowerBIWorkspace, Get-PowerBIReport, Get-PowerBIDataset, and so forth. These cmdlets are more convenient. Also, take a moment to check out the online help, which you can access through the Get-Help cmdlet. For example, use the command Get-Help Get-PowerBIWorkspace -Online to view the online documentation for working with workspaces.
Among other things, you might notice that almost all management cmdlets have an optional parameter called -Scope. This parameter can take one of two possible values: Individual and Organization. The default is Individual, which only returns those items that you can access as a regular Power BI user. On the other hand, if you are working as a Power BI admin, specify Organization to return all the items of a given type that exist in your Power BI tenant. Only Power BI users with admin rights (such as Office 365 Global Administrator or Power BI Service Administrator) can use the organization scope, but all users can use the individual scope, so the management cmdlets can really help any Power BI user automate repetitive tasks.
The following PowerShell script illustrates the difference between the individual and organization scope. The script illustrates both the use of the Get-PowerBIWorkspace cmdlet as well as comparable requests based on the Invoke-PowerBIRestMethod cmdlet, highlighting the fact that the individual scope invokes the user API whereas the organization scope relies on the admin API.
See also the screenshot below the listing for actual results in a test tenant.
## Login to Power BI using an account with service admin rights.
##
Login-PowerBI
## Get the list of workspaces as a Power BI user.
##
$myWorkspaces = Get-PowerBIWorkspace
Write-Host "The current user has --" $myWorkspaces.Count "-- workspaces."
## Get the list of workspaces as a Power BI admin.
##
$tentantWorkspaces = Get-PowerBIWorkspace -Scope Organization
Write-Host "There are --" $tentantWorkspaces.Count "-- workspaces in this Power BI tenant."
## Get the list of workspaces by making a direct REST API call against the user API.
## /v1.0/myorg/groups
##
$myRestResult = Invoke-PowerBIRestMethod -Url 'Groups' -Method Get | ConvertFrom-Json
Write-Host "The current user has --" $myRestResult.value.Count "-- workspaces."
## Get the list of workspaces by making a direct REST API call against the admin API.
## /v1.0/myorg/admin/groups
##
$tenantRestResult = Invoke-PowerBIRestMethod -Url 'admin/Groups' -Method Get | ConvertFrom-Json
Write-Host "There are --" $tenantRestResult.value.Count "-- workspaces in this Power BI tenant."
What happens if you call an admin API as a regular user without admin rights? As expected, PowerShell will display an error message informing you that the call was unauthorized. If you are among those users who would like to dig a little deeper, use the Resolve-PowerBIError cmdlet. If you run this cmdlet without any parameters, it shows the details for all errors that might have occurred in your current PowerShell session. You can specify -Last as a parameter to analyze only the last error or reference the desired error directly in the -Error parameter, such as -Error $Error[0]. The following script snippet uses the -Last parameter. The returned information highlights that the client tried to issue a GET request against https://api.powerbi.com/v1.0/myorg/admin/groups?$expand=users and that the service returned an HTTP 401 Unauthorized error.
## Login as a regular Power BI user without service admin rights.
##
Login-PowerBI
## Try to work with the organization scope.
##
Get-PowerBIWorkspace -Scope Organization
## Get additional troubleshooting information.
##
Resolve-PowerBIError -Last
Subsequent blog posts will cover typical scripting scenarios in more detail. Let’s finish this article with a brief list of typical tasks that the management cmdlets can help to accomplish efficiently:
- Automating recurring tasks using arbitrary REST API calls and third-party cmdlets without having to register an application in Azure—Log in to Power BI (Login-PowerBI), send arbitrary REST API calls to Power BI (Invoke-PowerBIRestMethod), retrieve the access token and pass it to any third-party cmdlets that accept an access token (Get-PowerBIAccessToken), and eventually log out (Logout-PowerBI).
- Enumerating all workspaces, dashboards, tiles, reports, datasets, data sources, and imports in a Power BI tenant—Log in to Power BI as an admin, and then use the cmdlets Get-PowerBIWorkspace, Get-PowerBIDashboard, Get-PowerBITile, Get-PowerBIReport, Get-PowerBIDataset, Get-PowerBIDatasource, and Get-PowerBIImport. Do not forget to specify -Scope Organization.
- Exporting all reports in a Power BI tenant to local files—Log in to Power BI as an admin, and then use the Export-PowerBIReport cmdlet. Do not forget to specify -Scope Organization and make sure you export each report into a separate file.
- Restore a deleted workspace—Login to Power BI, and then use the Get-PowerBIWorkspace cmdlet to enumerate the deleted workspaces and the Restore-PowerBIWorkspace cmdlet to restore them. Be aware that restoring workspaces is currently limited to the new workspace experiences preview.
- Renaming a workspace and adding or removing users—Log in to Power BI, and then use the Get-PowerBIWorkspace, Set-PowerBIWorkspace, Add-PowerBIWorkspaceUser, and Remove-PowerBIWorkspaceUser cmdlets. If you are updating a workspace you own, you don’t need to specify the organization scope, but be aware that updating workspaces is currently limited to the new workspace experiences preview.
- Enumerating workspaces that have a given user as a member—Log in to Power BI as an admin, and then use the Get-PowerBIGroup cmdlet with the -User parameter. Do not forget to specify -Scope Organization and be aware that membership information is currently limited to the new workspace experiences preview, so the command will not return personal or group-based legacy workspaces.
- Enumerating all orphaned workspaces (i.e. workspaces without an owner) in a Power BI tenant—Log in to Power BI as an admin, and then use the Get-PowerBIGroup cmdlet with the –Orphaned parameter. Do not forget to specify -Scope Organization. Again, this capability is currently limited to the new workspace experiences preview, so the command will not return any orphaned personal or group-based legacy workspaces.
- Enumerating all workspaces on a Premium capacity and then list the dashboards, tiles, reports, datasets, data sources, and imports in those workspaces—Log in to Power BI as an admin, and then use the cmdlet Get-PowerBIWorkspace with the -Filter parameter. Filter based on the capacity ID, such as Get-PowerBIGroup -Filter "capacityId eq '05801684-baba-4de0-8a6b-5445cf7df011'" -Scope Organization. Then pipe the output to Get-PowerBIDashboard, Get-PowerBITile, Get-PowerBIReport, Get-PowerBIDataset, Get-PowerBIDatasource, and Get-PowerBIImport to retrieve only the relevant resources from these workspaces. Do not forget to specify -Scope Organization because the -Filter parameter is currently not supported in the individual scope.
That’s it for the moment. As mentioned, subsequent blog posts will dive deeper into some of these scenarios. So, stay tuned for more information about how to work with Power BI in the PowerShell console. And, as always, please send us your feedback via any of the available communication channels such as UserVoice and community forums or simply go to https://github.com/Microsoft/powerbi-powershell/issues. You can influence the evolution of the Power BI management cmdlets to the benefit of all Power BI users!