• Antriksh Sharma

Get all Reports from Power BI Service using Rest API and PowerShell

Updated: Oct 25

Someone asked on Power BI Service on how to get all reports across all Premium workspaces so I started reading documentation and found that even a PowerShell noob like me can generate a piece of code for such request, so here we go.


First launch PowerShell ISE as an admin.

Next we need to install a module named MicrosoftPowerBIMgmt using the command

Install-Module -Name MicrosoftPowerBIMgmt

Once installed we can use the following method to Login to Power BI Service

Login-PowerBIServiceAccount

You will get a prompt like this one:

Once we are logged in we can invoke another method of getting all the workspacess in the tenant.


And we are going to store the collection of workspaces in a variable called $Workspaces

$Workspaces = Get-PowerBIWorkspace
$Workspaces

If you select the above code and run it it will return all the workspaces for you. If you need all the workspacess accross organization then use this

$Workspaces = Get-PowerBIWorkspace -Scope Organization
$Workspaces

It will also return those workspaces that have been deleted so you can use a foreach loop to filter out those.

$Workspaces = Get-PowerBIWorkspace -Scope Organization
#$Workspaces

foreach($W in $Workspaces){
    if($W.State -eq "Active"){
        Write-Output $W.Name
    }
}

If you want to keep Deleted ones just replace "Active" with "Deleted"


Okay moving on we can filter for Premium workspaces using IsOnDedicatedCapacity property:

$Workspaces = Get-PowerBIWorkspace -Scope Organization

foreach($W in $Workspaces){
    if($W.IsOnDedicatedCapacity -eq $true){
        Write-Output $W.Name
    }
}

My Premium workspaces have PPU suffixed so that it is easy to confirm.

But as you can see even My Workspace appears because I have changed the type to PPU so to exclude that we can add another condition in the IF statement:

Now once we have a collection of target workspaces we can iterate on the reports in them. To get reports as a collection we can write the following code:

$Reports = Get-PowerBIReport -WorkspaceId $w.Id

And then we can loop on the collection using another foreach loop

Finally to make it more obvious which report belongs to which workspace we can concatenate workspace and report name.

If you want to show something like "No Reports" for the workspaces that do not have any report you can add another check:

if($Reports.Count -eq 0){
    Write-Output "$($W.Name) - No Reports"
}

Here is the complete code:

#Install-Module -Name MicrosoftPowerBIMgmt
#Login-PowerBIServiceAccount

CLS # Clear output window
$Workspaces = Get-PowerBIWorkspace -Scope Organization

foreach($W in $Workspaces){
    if($W.IsOnDedicatedCapacity -eq $true -and $W.Type -eq "Workspace"){
        $Reports = Get-PowerBIReport -WorkspaceId $W.Id -Scope Organization
        if($Reports.Count -eq 0){
            Write-Output "$($W.Name) - No Reports"
        }
        else{
            foreach($R in $Reports){
                Write-Output "$($W.Name) - $($R.Name)"
            }
        }
    }
}
55 views0 comments