PowerShell Cmdlets for Power BI: What is it? And Why should you care?

Power BI has a set of PowerShell Cmdlets that help automate part of the operations with Power BI. However, PowerShell is not a commonly familiar technology. In the world of Power BI, we are used to working with graphical options and settings provided in the tools and the service. However, using commands provided for Power BI in a command/scripting tool such as PowerShell can be an excellent asset for a Power BI administrator, architect, and developer. In this article and video, you will learn about the PowerShell Cmdlets for Power BI, what they are, how they can be helpful, and examples of using them.

Video

PowerShell

PowerShell is a task automation and configuration management program from Microsoft, consisting of a command-line shell and the associated scripting language.

https://en.wikipedia.org/wiki/PowerShell

PowerShell has become a common tool for administrators over the past few years. As an administrator, there are many libraries that you can access using PowerShell modules. PowerShell’s scripting and command line experience are not as complicated as learning a programming language (such as C#.NET). This results in a tool that can be used but gives great power in configuration and task automation. Learning PowerShell itself is a topic that is outside of this article. The PowerShell documentation can be a good start for it.

PowerShell Cmdlets for Power BI

PowerShell includes modules that provide access to certain functionalities. Consider these modules as libraries. Each will give you access to certain objects and configurations in the Power BI Service.

For example, one module gives you access to the list of workspaces in the Power BI service, and another helps you with the reports inside the workspace. You can use a module to capture activities through the Power BI tenant in your organization. You can use a combination of these modules to extract a report from one workspace and then export it to another.

Below is the list of the modules.

Description	Module Name	PowerShell Gallery link
Rollup module for Power BI Cmdlets	MicrosoftPowerBIMgmt
Admin module for Power BI Cmdlets	MicrosoftPowerBIMgmt.Admin
Capacities module for Power BI Cmdlets	MicrosoftPowerBIMgmt.Capacities
Data module for Power BI Cmdlets	MicrosoftPowerBIMgmt.Data
Profile module for Power BI Cmdlets	MicrosoftPowerBIMgmt.Profile
Reports module for Power BI	MicrosoftPowerBIMgmt.Reports
Workspaces module for Power BI	MicrosoftPowerBIMgmt.Workspaces

If you install the very first module, it includes all the other modules. You can also choose to install modules as you need.

To use the modules above, you will need to have the following requirements;

• 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.

Getting Started

Let’s go through a sample usage of these modules. To start, open Windows PowerShell ISE (this is a more user-friendly version of Windows PowerShell, If you don’t have this application, use the normal Windows PowerShell). It is better if you start this application as an administrator.

This will open the application below, in which you can either type the commands (also called Cmdlets) or use the list on the right-hand side and select from the cmdlets (this list is only available in ISE, the basic Windows PowerShell doesn’t come with it).

To get the modules to install, you can use the Install-Module Cmdlet as below;

Install-Module -Name MicrosoftPowerBIMgmt

Then confirm the installation of the module

This will install the module. If you already have the module installed and want to update it to the most recent version, use the Update-Module Cmdlets with the same parameters.

To use the Power BI Cmdlets, first, you need to log in to Power BI using Connect-PowerBIServiceAccount Cmdlet as below;

Connect-PowerBIServiceAccount

Or, if you prefer to use the list of modules in the PowerShell ISE, Refresh the modules list, then select Power BI Profile module, and select the Cmdlet under that. You can either Run the command from here or insert it, which will insert in the command line.

If you choose a Cmdlet that requires parameters, the UI will also have the places to enter those details.

Use the sign-in box to use your account for the Power BI. If your account is a Power BI administrator, it might give you tenant results in your command execution. Otherwise, you can still use your non-admin Power BI account to use some of the commands.

If the login is successful, you will see your tenant Id and the username.

Now you can test some of the Cmdlets and see the result. For example, the Cmdlet below gives you a list of all workspaces (these would be workspaces that you have access to, not all workspaces in the organization);

Get-PowerBIWorkspace -All

The output will be a list of workspaces, their names with IDs, and other information.

Sample Scenario: Export a list of all workspaces in the organization as a CSV file

If your account is a Power BI Administrator account, then you can use a parameter for scope and set that to Organization, and this will give you all the workspaces in your tenant (even if you don’t have access to those workspaces and other users in your organization create them);

Get-PowerBIWorkspace -Scope Organization -All

As I mentioned, you can also select this Cmdlet in the list on the right-hand side and see all other parameters available.

The command above might take some time to run if you have many workspaces in your organization.

Now comes the power of PowerShell itself. The output above is great, but what if you could export that as a file (which you can use for other applications and purposes)? Many modules and Cmdlets in PowerShell can help you in general tasks like that. In the below, we use the ConvertTo-Csv Cmdlet with the Out-File parameter, which specifies the path of the CSV file.

Get-PowerBIWorkspace -Scope Organization -All | ConvertTo-Csv | Out-File c:\PowerBIWorkspaces.csv

The command above gives you a CSV file export of all workspace details as below;

Isn’t this helpful? Now imagine if you can schedule these simple few lines of script to run every night from a server and store list of workspaces. Now you have an application that stores the history of workspace changes throughout time in your organization. It is needless to say that you can generate the file name using Date and timestamp easily. So, if you are tasked to have a history of all workspaces throughout the entire organization at any certain point in time? No problem, you have the script above; schedule it. Here is an example of scheduling a PowerShell script using Task Scheduler.

Sample Scenario: Deploy a Power BI report from one workspace to another

PowerShell Cmdlets for Power BI can also create a deployment pipeline solution. This, then, can be a good replacement for the Deployment Pipeline feature, which is a Premium function in Power BI (of course, building something as comprehensive as that might take a while using the PowerShell Cmdlets).

I am going to show you part of this as an example. Let’s say we want to export a single report from one workspace to another. We can use a combination of Cmdlets for this purpose, including Get-PowerBIReport and Export-PowerBIReport.

You need to know the workspace Id to get a Power BI report from a workspace. The workspace Id is a unique identifier. This Id can be recognized in the page URL when you are inside the workspace.

The same workspace Id is also returned as the result of PowerShell Cmdlets such as Get-PowerBIWorkspace.

This workspace Id can now be used as a parameter for the Get-PowerBIReport Cmdlet to give you the list of all reports under the workspace.

Get-PowerBIReport -WorkspaceId <id of your workspace> 

The result below shows the output.

Now to export the PBIX file of one of these reports, you can use the Report Id and the output file name like below;

Export-PowerBIReport -Id <report Id> -OutFile "c:\CSNA Movies.pbix"

This download the PBIX file for that report into the local path.

Now we can publish this PBIX file to another workspace. You will need the workspace Id of the destination workspace, and you can use the Cmdlet below;

New-PowerBIReport -Path "C:\CSNA Movies.pbix" -WorkspaceId <workspace id>

The report is now copied into this destination workspace.

This action is different from the Copy-PowerBIReport Cmdlet. The difference between these two is that the Copy Cmdlet will create a live connection to the dataset in the source workspace, whereas the method I explained will create a copy of the dataset too.

The method above can be expanded into looping through all the reports in the source workspace and exporting them into the destination workspace, like a full deployment pipeline from the Development environment into the Test or Production environment. However, explaining such a process is outside of this article’s scope.

Is PowerShell Cmdlets of Power BI the same as REST APIs for Power BI?

One might ask, “Are the PowerShell Cmdlets for Power BI the same as the REST API for Power BI?” Answer to this question is; Yes, and No. These Cmdlets are designed on the base of the REST API functions. However, the capabilities available in the REST APIs are far more than the PowerShell Cmdlets. PowerShell Cmdlets are kind of a layer on top of REST APIs. We do not have a Cmdlet for every REST API; there are functions that you can only do through REST API.

One big benefit of PowerShell Cmdlets compared to REST API, however, is that To use the PowerShell Cmdlets, you don’t need to be a C# or VB.NET developer. PowerShell scripts are much simpler to use and learn. An administrator can use it with a few tries, errors, and a combination of searches in the online documentation.

However, if you need more extensive usage of APIs, then REST API usage through a custom application is the way to go. If you have seen the capabilities in the Power BI Helper to help with some documentation in the service and exporting reports from one workspace to another, those are implemented using the .NET REST API of Power BI.

Summary

In a nutshell, Power BI has a set of modules and Cmdlets for PowerShell. These are simple to use and, combined with the generic Cmdlets in Power BI, will give the user abilities such as the scenarios mentioned in this article (creating deployment scripts or generating documentation on a time-based schedule). Learning PowerShell is not as complex as learning a programming language. This, combined with the ability to connect to Power BI service objects from PowerShell, enables Power BI administrators and developers to leverage these functionalities to automate some scenarios. The Cmdlets and modules are still a work in progress; however, they are always behind the REST API functionality. If you need more extensive capabilities through programming to the Power BI objects, then REST API can be a better option.

Reza Rad

Trainer, Consultant, Mentor

Reza Rad is a Microsoft Regional Director, an Author, Trainer, Speaker and Consultant. He has a BSc in Computer engineering; he has more than 20 years’ experience in data analysis, BI, databases, programming, and development mostly on Microsoft technologies. He is a Microsoft Data Platform MVP for 12 continuous years (from 2011 till now) for his dedication in Microsoft BI. Reza is an active blogger and co-founder of RADACAD. Reza is also co-founder and co-organizer of Difinity conference in New Zealand, Power BI Summit, and Data Insight Summit.
Reza is author of more than 14 books on Microsoft Business Intelligence, most of these books are published under Power BI category. Among these are books such as Power BI DAX Simplified, Pro Power BI Architecture, Power BI from Rookie to Rock Star, Power Query books series, Row-Level Security in Power BI and etc.
He is an International Speaker in Microsoft Ignite, Microsoft Business Applications Summit, Data Insight Summit, PASS Summit, SQL Saturday and SQL user groups. And He is a Microsoft Certified Trainer.
Reza’s passion is to help you find the best data solution, he is Data enthusiast.
His articles on different aspects of technologies, especially on MS BI, can be found on his blog: https://radacad.com/blog.