The Power BI-PowerShell cmdlet cheat sheet

Acquire the Power BI PowerShell module

Native PowerShell's library of cmdlets is only sufficient for managing the Windows operating system. It doesn't include support for external products like Power BI, meaning that users will need to download Microsoft's Power BI management module for PowerShell before they can perform any Power BI management tasks from the command line.

In PowerShell, a module is a collection of cmdlets related to a specific product or task. As such, the Power BI management module contains all of the cmdlets necessary to manage Power BI from PowerShell. You can download the module from Microsoft's PowerShell website.

Install the module

If you're new to working with PowerShell modules, it's important to know that installing one isn't like installing an application on your computer. When you install a new application, it remains available for your use until you uninstall it; in the case of PowerShell modules, a new module remains on your computer as you would expect, but the module only remains installed for the duration of the session. The next time you need to use the module, you'll have to reinstall. The command used to do so is:

Install-Module -Name MicrosoftPowerBIMgmt

Once the module is installed, you can see a list of all the cmdlets that are included by entering the following command:

Get-Command -Module MicrosoftPowerBIMgmr

Connect to Power BI with PowerShell

Once the required module is installed, you'll have to connect PowerShell to your Power BI service account. The exact method used to do this varies depending on whether you're using a set of credentials or a certificate, and whether you need to specify an environment. If you want to log in using a set of credentials, you can do so with this command:

Connect-PowerBIServiceAccount -ServicePrincipal -Credential (Get-Credential)

If you want to log in by specifying a certificate thumbprint, you would use this command instead:

Connect-PowerBIServiceAccount -ServicePrincipal -CertificateThumbprint <certificate thumb print number>

You can find the full syntax of the Connect-PowerBIServiceAccount cmdlet in Microsoft's documentation.

Once you've successfully made the Power BI-PowerShell connection, there are three main things that you can manage from PowerShell -- data sets, workspaces and reports.

Cmdlets to manage data sets

You can use PowerShell to create and manage Power BI data sets. For example, if you wanted to create a new data set, you could do so by using the New-PowerBIDataset cmdlet. If you wanted to see a list of the existing data sets, you could use the Get-PowerBIDataset cmdlet. PowerShell also includes functionality to create tables and columns, and to manage sources, as shown in the table below.

Manage workspaces

You can get PowerShell to display a list of all of the Power BI workspaces with the Get-PowerBIWorkspace cmdlet. Although PowerShell doesn't include a cmdlet to create new workspaces, you can use PowerShell to add or remove user permissions from a workspace, or to update a workspace.

Manage reports

The main report-related tasks that most people will probably want to perform through PowerShell are to list and then export reports. These tasks can be accomplished with the Get-PowerBIReport and Export-PowerBIReport cmdlets. Cmdlets also are available for listing PowerBI imports, dashboards and tiles, which are data snapshots that can be pinned to a dashboard.

Users should experiment with the available Power BI-PowerShell cmdlets to figure out which combinations work best for their business and their BI application needs.