What are Power BI PowerShell Cmdlets?
Power BI PowerShell Cmdlets refers to a set of commands that you can execute to enable you to manage and automate different tasks efficiently. It comes with a set of APIs and cmdlets that can connect with workspaces, dashboards, reports and data sources in Power BI. Power BI Powershell Cmdlets are available as a module from the Powershell gallery which makes it easy to install.
Powershell Cmdlets can help you in performing several tasks which include:
- Automating repetitive tasks using Power BI REST API calls and third-party Cmdlets.
- Summarize your workspaces, dashboards, tiles, Power BI reports, and data sources.
- Export your reports into workspaces.
- Restore any deleted workspaces.
- Recover orphaned workspaces.
- Rename your workspaces and add/delete users.
- List out all the reports, dashboards, tiles, and data sources in orphaned workspaces or for a member user.
Table of contents
Key Takeaways
- Power BI PowerShell Cmdlets represent a set of commands to manage and perform task automation efficiently.
- It is a bundled package of APIs and cmdlets that help you connect with workspaces, dashboards, reports, and data sources in Power BI.
- With Power BI PowerShell Cmdlets, you can perform a series of administrative tasks such as listing out the reports, dashboards, tiles, imports, and data sources, exporting your reports into workspaces, and restoring your deleted workspaces, etc.
- To use the PowerShell Cmdlets, you need to Install the Power BI PowerShell cmdlets MicrosoftPowerBIMgmt module or individual modules.
- You can authenticate to Power BI using either a user account or a service principal account.
- Power BI PowerShell provides you the flexibility to automate report generation, refresh datasets using Power BI REST API, and even handle errors.
How to use Power BI PowerShell Cmdlets?
As a pre-requisite to using Power BI PowerShell Cmdlets, ensure that you have the following versions and environments.
- Windows PowerShell v3.0 and up with .NET 4.7.1 or above.
- PowerShell Core (v6) and up on any OS platform supported by PowerShell Core.
To use Power BI PowerShell Cmdlets, follow the instructions outlined below:
Step 1: Install Power BI PowerShell cmdlets modules from the PowerShell gallery, i.e., MicrosoftPowerBIMgmt. Use the following command.
Install-Module -Name MicrosoftPowerBIMgmt
It will install all the modules in MicrosoftPowerBIMgmt. Alternatively, you can Install Power BI PowerShell cmdlets individual modules as per your requirements.
If you have any earlier versions of the Power BI PowerShell Cmdlets modules, you can update the latest version by using the below syntax:
Update-Module -Name MicrosoftPowerBIMgmt
Step 2: Connect with the Power BI Service using the following syntax.
Connect-PowerBIServiceAccount
You can either use individual or organization scope cmdlets to interact with Power BI.
- Individual – use this scope to access the user-created entities.
- Organization – use this scope to access the entities across the firm. This can only be accessed by Power BI tenant admins.
Step 3: Get the workspaces applicable to the user using the syntax below:
Get-PowerBIWorkspace
You can customize the list of workspaces by either opting for all workspaces or restricting it to the first 100 workspaces applicable to the user. Use -Scope Organization to view all workspaces in your organization.
Get-PowerBIWorkspace -Scope Organization -All
Step 4: Add new users or remove existing users from your workspace as shown below.
Add-PowerBIWorkspaceUser -Scope Organization -Id 3244f1c1-01cf-457f-9383-6035e4950fdc -UserEmailAddress mary@walmart.com -AccessRight Admin #Add new users
Remove-PowerBIWorkspaceUser -Scope Organization -Id 3244f1c1-01cf-457f-9383-6035e4950fdc -UserEmailAddress mary@walmart.com #Remove existing users
Step 5: Restore a deleted workspace or recover an orphaned workspace
Get-PowerBIWorkspace -Scope Organization -Deleted -All #Restore deleted workspace
Get-PowerBIWorkspace -Scope Organization -Orphaned -All’ #Recover orphaned workspace
Step 6: You can extract all the reports, dashboards, tiles, and imports using the PowerShell Cmdlets commands
Get-PowerBIReport #All reports for the user
Get-PowerBIDashboard -Scope Organization #All reports for the organization
Get-PowerBIImport #All Power BI imports
Step 7: You can also create reports, export datasets, get Power BI datasets, and Power BI tables in a dataset.
New-PowerBIReport -Path .\newReport.pbix -Name ‘New Report’ #create new reports
Export-PowerBIReport -Id b48c088c-6f4e-4b7a-b015-d844ab534b2a -OutFile .\exportedReport.pbix #Export reports
Get-PowerBIDataset #extract all datasets
Step 8: Invoke PowerBIRestMethod to call the Power BI REST API for authentication
Invoke-PowerBIRestMethod -Url ‘reports/4eb4c303-d5ac-4a2d-bf1e-39b35075d983/Clone’ -Method Post -Body ([pscustomobject]@{name=’Cloned report’; targetModelId=’adf823b5-a0de-4b9f-bcce-b17d774d2961′; targetWorkspaceId=’45ee15a7-0e8e-45b0-8111-ea304ada8d7d’} | ConvertTo-Json -Depth 2 -Compress)
Examples
In this section, we will understand Power BI PowerShell Cmdlets through some examples.
Example #1 – Export a list of all workspaces in the organization as a CSV file
In this example, we will demonstrate how to export a list of all workspaces in the organization as a CSV file. As a pre-requisite, ensure that you have Power BI tenant admin rights.
To export the list of all workspaces, follow the steps below:
Step 1: Install Power BI PowerShell cmdlets modules from the MicrosoftPowerBIMgmt.
Install-Module -Name MicrosoftPowerBIMgmt
Step 2: Log in to Power BI Service using an account with service admin rights.
Connect-PowerBIServiceAccount # or use aliases: Login-PowerBIServiceAccount, Login-PowerBI
Get the list of workspaces applicable to your organization.
$workspaces = Get-PowerBIWorkspace -Scope Organization #Get the list of workspaces for your organization as Power BI admin
Write-Host “In total, you have –” $workspaces.Count “– workspaces in your organization Power BI tenant.”
Step 4: Convert the details into CSV.
$csv = ConvertTo-csv -Object $workspaces
Step 5: Save your CSV file.
$csv | Out-File “workspaces.csv”
Step 6: Provide a confirmation message to the end user.
Write-Host “Total list of workspaces list is successfully exported to workspaces.csv”
Example #2 – Deploy a Power BI report from one workspace to another
In this example, we will understand how you can deploy a Power BI report from one workspace to another.
To deploy a Power BI report from one workspace to another, follow the steps below:
Step 1: Install the Power BI PowerShell cmdlets workspace module from the MicrosoftPowerBIMgmt. Here, we have installed only the workspace module. You can also install the entire rollup module.
Install-Module -Name MicrosoftPowerBIMgmt.Workspaces
Step 2: Log in to Power BI Service using an account with service admin rights.
Connect-PowerBIServiceAccount
Step 3: Enumerate all the reports in your workspace.
$report = Get-PowerBIReport -WorkspaceId “<current_workspace_id>” -ReportId “<report_id>”
Step 4: Deploy your reports to the target workspace.
$report | Add-PowerBIReport -WorkspaceId “<target_workspace_id>”
Step 5: Once the PowerShell Cmdlets are executed successfully, provide the confirmation message to the users.
Write-Host “Your Power BI Report is deployed from the current workspace to the target workspace”
Example #3 – Listing Dashboards and Tiles
In this example, we will understand how you can list dashboards and tiles using PowerShell Cmdlets.
To list the dashboards and tiles, follow the instructions below:
Step 1: Install Power BI PowerShell cmdlets modules from the MicrosoftPowerBIMgmt. You can use the syntax below to install the rollup modules from PowerShell Gallery.
Install-Module -Name MicrosoftPowerBIMgmt
Step 2: Log in to Power BI Service using an account with service admin rights.
Connect-PowerBIServiceAccount # or use aliases: Login-PowerBIServiceAccount, Login-PowerBI
Step 3: Get the list of all dashboards in your organization tenant by adding -Scope Organization:
$dashboards = Get-PowerBIDashboard -Scope Organization #Get the list of dashboards for your organization as Power BI admin
Write-Host “In total, you have –” $ dashboards.Count “– Dashboards in your organization Power BI tenant.”
Step 4: Get tiles within a dashboard.
$tiles = Get-PowerBITile -DashboardId <dashboard-id>
Write-Host “In total, you have –” $ tiles.Count “– tiles in your dashboard.”
Step 5: You can enumerate the entire list of dashboards to identify the total number of tiles.
The following code snippet can be used to list out the entire list of tiles.
# Get dashboards in existing user’s workspace
$dashboards = Get-PowerBIDashboard -Scope Organization #Get the list of dashboards
# Get the list of tiles for each dashboard
$tiles = foreach ($dashboard in $dashboards) {
Get-PowerBITile -DashboardId $dashboard.Id
}
# Display dashboard and tile information
Write-Host “Dashboard Details:”
foreach ($dashboard in $dashboards) {
Write-Host ” – $dashboard.Name”
}
Write-Host “Tile Details:”
foreach ($tile in $tiles) {
Write-Host ” – $tile.Title from $tile.DashboardName”
}
Important Things to Note
- You need to have either Power BI Pro or Power BI Premium capacity to access PowerShell Cmdlets.
- If you are using -Scope Organization, you need to have Power BI tenant admin access.
- Consider implementing error-handling validation checks to safeguard your scripts
- Always ensure that you have the latest version of the Power BI PowerShell Cmdlets installed in your system for automating the tasks
- For reporting any bugs or issues related to Power BI PowerShell Cmdlets, refer to the Microsoft portal for submitting the issue details. You can also refer to the Power BI community or Power BI Support portal for your issues.
- As a best practice, always ensure that the components are tested in non-production environments before you deploy them in the live environment.
Frequently Asked Questions (FAQs)
You can authenticate to Power BI with either a user account or service principal account using PowerShell.
• User account
◦ Log into PowerBIServiceAccount using user authentication against the Public cloud using the syntax Connect-PowerBIServiceAccount.
◦ Enter the user account credentials when prompted.
• Service principal account
◦ Log into the PowerBIServiceAccount using a service principal against the public cloud and enter the username(AAD client ID) and password(application secret key) using the syntax Connect-PowerBIServiceAccount -Tenant [TenantId] -ServicePrincipal -Credential (Get-Credential)
You can automate report generation with Power BI PowerShell by using Powershell which will automate the below processes:
• Log into Power BI as an administrator
• Use the cmdlets Get-PowerBIReport to get all the reports for the user. To create new reports, use New-PowerBIReport by uploading .pbix files
• Export all the reports using the Export-PowerBIReport cmdlet.
For viewing all the reports in your organization tenant, use – Scope Organization.
You can also use MicrosoftPowerBIMgmt.Reports PowerShell cmdlets for managing and interacting with Power BI reports.
To refresh a dataset in Power BI using PowerShell, follow the instructions below:
• Import the Power BI Management module
• Log into the Power BI service leveraging the Azure AD credentials
• Execute the Invoke-PowerBIRestMethod cmdlet and call the Power BI REST API to refresh the dataset.
To handle errors in PowerShell scripts with Power BI cmdlets, you can use the Resolve-PowerBIError cmdlet. This will help you to get more information about any error raised from the cmdlets. You can use the following syntax to troubleshoot the errors:
Resolve-PowerBIError -Last
This will always return the last encountered error details.
Recommended Articles
This has been a guide to Power BI PowerShell Cmdlets. Here we learn how to use PowerShell Cmdlets in power bi, with examples, and points to remember. You can learn more from the following articles –
Leave a Reply