In today’s post, we will discuss how to automate exporting Power BI Workspaces, Datasets, Reports and Dashboards using PowerShell.

Step one:

To use PowerShell cmdlets for managing and interacting with Power BI, you first need to install the module: MicrosoftPowerBIMgmt

Install-Module -Name MicrosoftPowerBIMgmt

Step two:

Sign in to Power BI using below command, a prompt will pop up to authenticate to Power BI. For this example, I am authenticating as Power BI Admin.

Connect-PowerBIServiceAccount

Note: You can also authenticate using service principal.

For unattended login, use below script or register an app for service principal using Example 4 in this documentation.

## unattended login to Power BI Service
$username = “username”
$password = “password” | ConvertTo-SecureString -asPlainText -Force
$credential = New-Object System.Management.Automation.PSCredential($username, $password)
Connect-PowerBIServiceAccount -Credential $credential

Step three:

To export the files to csv, use a variable to store the values. For this example, I have 4 variables to store the list of Workspaces, Reports, Datasets and Dashboards respectively:

$workspaces

$reports

$datasets

$dashboards

Use foreach to loop through the Workspaces and its contents.

Run the script to confirm its successful and save in a location of your choice, for this example I saved the script as Automation.ps1 in my c drive temp folder.

## Get list of Workspaces, reports, datasets, dashboards and export to csv file in the current working directory
$workspaces = Get-PowerBIWorkspace -Scope Organization
$workspaces | Export-Csv “workspaces.csv” -NoTypeInformation

@(“reports”, “dashboards”, “datasets”) | foreach {
                $csvFile = $_ + “.csv”
                if(Test-Path $csvFile){ Remove-Item $csvFile }
            }

foreach($workspace in $workspaces)
{
    $reports = Get-PowerBIReport -WorkspaceId $workspace.id -Scope Organization;
    $reports | Export-Csv “reports.csv” -NoTypeInformation -Append;

    $dashboards = Get-PowerBIDashboard -WorkspaceId $workspace.id -Scope Organization;
    $dashboards | Export-Csv “dashboards.csv” -NoTypeInformation -Append;

    $datasets = Get-PowerBIDataset -WorkspaceId $workspace.id -Scope Organization;
    $datasets | Export-Csv “datasets.csv” -NoTypeInformation -Append;
}

Step four:

Now we have automated all steps, its time to run the script. To run the script, simply change the directory to where the script was saved, type cd c:\temp at the prompt and type the name of the script to run it, type .\Automation.ps1 at the prompt.

Depending on the number of Workspaces and contents, this may take several minutes or hours to complete. When the script finished running, check the folder for the exported files:

Now you can connect to the folder as a datasource from Power BI Desktop to create reports and automate refresh on Power BI Service, or review as it is from the csv files.

Happy Power BI Administration 🙂