Connecting to Power BI using PowerShell

PowerShell can be used to connect to Power BI, manage datasets, refresh reports, assign workspaces, and automate administrative tasks. This guide walks you through connecting to Power BI using Power BI REST API and Power BI PowerShell Modules.

Step 1: Prerequisites

1. Install Required Modules

Before connecting, ensure you have the necessary PowerShell modules:

# Install Power BI module
Install-Module -Name MicrosoftPowerBIMgmt -Scope CurrentUser -Force

# Import the module
Import-Module MicrosoftPowerBIMgmt

This module includes cmdlets for managing datasets, workspaces, and users in Power BI.

2. Required Permissions

You must be a Power BI Admin or have Power BI Service Administrator privileges.
You need Azure AD permissions to authenticate via a registered Azure App (for automation).

Step 2: Connecting to Power BI

Option 1: Interactive Login (For Manual Use)

If you’re manually running PowerShell scripts, you can authenticate interactively using your Power BI account:

# Connect interactively
Connect-PowerBIServiceAccount

A pop-up window will prompt you to log in with your Microsoft 365 account.

Option 2: Using Service Principal (For Automation)

For automation, use an Azure AD App Registration with client credentials.

1. Register an App in Azure AD

Go to Azure Portal → Azure Active Directory → App registrations.
Click + New Registration and provide a name.
In API Permissions, add Power BI Service (Application permissions):
- Dataset.ReadWrite.All
- Report.ReadWrite.All
- Workspace.ReadWrite.All
Generate a client secret and note down the Application (Client) ID and Tenant ID.

2. Connect Using Service Principal

# Define credentials
$clientId = "your-app-client-id"
$clientSecret = "your-app-client-secret"
$tenantId = "your-tenant-id"

# Convert the secret to a secure string
$secureSecret = ConvertTo-SecureString $clientSecret -AsPlainText -Force
$credential = New-Object System.Management.Automation.PSCredential ($clientId, $secureSecret)

# Connect to Power BI using Service Principal
Connect-PowerBIServiceAccount -ServicePrincipal -TenantId $tenantId -ClientId $clientId -Credential $credential

Now connected to Power BI!

Step 3: Verifying Connection

To check if you’re successfully connected, run:

Get-PowerBIWorkspace

If the connection is successful, you’ll see a list of Power BI workspaces.

Step 4: Managing Power BI Workspaces

1. Get All Workspaces

Get-PowerBIWorkspace

2. Get Details of a Specific Workspace

Get-PowerBIWorkspace -Name "Sales Reports"

3. Create a New Workspace

New-PowerBIWorkspace -Name "Marketing Analytics"

Step 5: Managing Reports & Datasets

1. List All Reports

Get-PowerBIReport

2. Refresh a Dataset

# Get dataset ID
$dataset = Get-PowerBIDataset -Name "Sales Data"

# Refresh the dataset
Invoke-PowerBIRestMethod -Url "groups/$($dataset.Id)/datasets/refreshes" -Method Post

3. Export a Report as PDF

# Define report ID
$reportId = "your-report-id"
$workspaceId = "your-workspace-id"

# Export to PDF
Invoke-PowerBIRestMethod -Url "groups/$workspaceId/reports/$reportId/ExportTo" -Method Post

Step 6: Automating Power BI Tasks

1. Schedule a Daily Dataset Refresh

# Create a scheduled task to run the refresh script daily at 2 AM
$taskAction = New-ScheduledTaskAction -Execute "powershell.exe" -Argument "-ExecutionPolicy Bypass -File C:\Scripts\PowerBI_Refresh.ps1"
$taskTrigger = New-ScheduledTaskTrigger -Daily -At 2AM
Register-ScheduledTask -TaskName "Power BI Dataset Refresh" -Action $taskAction -Trigger $taskTrigger -User "NT AUTHORITY\SYSTEM" -RunLevel Highest -Force

Dataset refreshes every day at 2 AM!

Step 7: Disconnecting from Power BI

To properly disconnect from Power BI:

Disconnect-PowerBIServiceAccount