Exporting Power BI Report Metadata Using PowerShell

Exporting Power BI report metadata helps administrators track and manage reports, datasets, workspaces, and user access efficiently. This guide will walk you through how to retrieve and export Power BI report metadata using the MicrosoftPowerBIMgmt PowerShell module.

Step 1: Prerequisites

1. Install and Import the Power BI PowerShell Module

Ensure that the Power BI PowerShell module is installed:

# Install the Power BI module (if not installed)
Install-Module -Name MicrosoftPowerBIMgmt -Scope CurrentUser -Force

# Import the module
Import-Module MicrosoftPowerBIMgmt

2. Authenticate to Power BI

Before retrieving report metadata, connect to Power BI:

# Connect interactively
Connect-PowerBIServiceAccount

For automation, use a Service Principal:

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

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

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

Now you’re connected to Power BI!

Step 2: Retrieve Power BI Report Metadata

To get metadata for all reports, use:

# Get all reports and their metadata
$reports = Get-PowerBIReport -All

# Display metadata details
$reports | Select-Object Id, Name, WebUrl, DatasetId, WorkspaceId, ModifiedDate

Example Output

Id                                   Name              WebUrl                        DatasetId                               WorkspaceId                           ModifiedDate
------------------------------------ ---------------- ----------------------------- ------------------------------------ ------------------------------------ --------------
abcd1234-5678-90ef-ghij Sales Report https://app.powerbi.com/reports/abcd1234 efgh5678-1234-90kl-mnop wxyz9876-5432-10ab-cdef 2024-03-15

You now have Power BI report metadata!

Step 3: Retrieve Report Metadata for a Specific Workspace

To retrieve metadata for a specific workspace, first get the workspace ID:

# Define workspace name
$workspaceName = "Sales Analytics"

# Get workspace details
$workspace = Get-PowerBIWorkspace -Name $workspaceName

# Retrieve report metadata for the workspace
$reports = Get-PowerBIReport -WorkspaceId $workspace.Id

# Display metadata
$reports | Select-Object Id, Name, WebUrl, DatasetId, ModifiedDate

Report metadata for the specific workspace retrieved!

Step 4: Retrieve Additional Metadata (Datasets, Owners, etc.)

To get dataset details linked to reports:

# Get all datasets
$datasets = Get-PowerBIDataset -All

# Display dataset metadata
$datasets | Select-Object Id, Name, WebUrl, IsRefreshable, ModifiedDate

To get report owners and access details:

# Get report access details
$reports | ForEach-Object { 
    Get-PowerBIReportUser -ReportId $_.Id 
}

You now have complete metadata including datasets and ownership details!

Step 5: Export Report Metadata to CSV

To save report metadata to a CSV file:

$reports | Export-Csv -Path "C:\PowerBI_Report_Metadata.csv" -NoTypeInformation

For datasets metadata:

$datasets | Export-Csv -Path "C:\PowerBI_Datasets_Metadata.csv" -NoTypeInformation

Metadata exported successfully!

Step 6: Disconnect from Power BI

Once done, disconnect from Power BI:

Disconnect-PowerBIServiceAccount