Automate Export of Power BI Contents using PowerShell
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
![](https://i0.wp.com/rukiatu.com/wp-content/uploads/image-174.png?resize=640%2C105&ssl=1)
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.
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.
![](https://i0.wp.com/rukiatu.com/wp-content/uploads/ps5.png?resize=640%2C486&ssl=1)
## 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.
![](https://i0.wp.com/rukiatu.com/wp-content/uploads/ps4.png?resize=640%2C321&ssl=1)
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:
![](https://i0.wp.com/rukiatu.com/wp-content/uploads/ps2-1.png?resize=640%2C203&ssl=1)
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 🙂
One thought on “Automate Export of Power BI Contents using PowerShell”
Awesome post and very helpful! Thanks for sharing, Ruki!
Comments are closed.