Get data from Power BI service with PowerShell Cmdlets

We had some cases where we would like to check what information was within the Power BI service and be able to make a decision in the BI area, based on this data.

Therefore, one way we found to access this data and be able to work it in a database or even within an Excel spreadsheet, was extracting the data through a script in PowerShell and exporting it in a CSV file.

I’m not a PowerShell expert, but after looking at forums, official documentation and even talking to Microsoft support, I managed to solve the problem we had with the solutions below. So if there is any easier way to complete this proposed solution or good practices that have not been carried out, I apologize in advance.

So, before showing the PowerShell script, it is necessary that the user who will access the service must be an admin user in order to have access to the data without restriction.

To run the PowerShell script it will also be necessary to install the PowerBI modules available at the link below, if the module is not installed, it will not be possible to run the script successfully. Preferably install the module “MicrosoftPowerBIMgmt.Admin”, which is the Admin module for Power BI Cmdlets, but it’s necessary to install the modules for each problem.

Power BI Cmdlets reference

After installing the module successfully, you should save the script below and run it in PowerShell.

The first script, obtains the data using the module “Get-PowerBIWorkspace”, and its function is to return a list of Power BI workspaces, all documentation can be found in the link below as well.

Get-PowerBIWorkspace (MicrosoftPowerBIMgmt.Workspaces)

# To execute the script without agreeing with the execution policy
Set-ExecutionPolicy Bypass -Scope Process

# Import the Power BI admin module
Import-Module MicrosoftPowerBIMgmt.Admin

# Your email to connect to the Power BI service
$username = "Your-Email" 

# Your password to connect to the PowerBI service
$password = "Your-Password" | ConvertTo-SecureString -asPlainText -Force

# Run the credential according to the login and password above
$credential = New-Object System.Management.Automation.PSCredential($username, $password)

# Connect to the Power BI service using credentials
Connect-PowerBIServiceAccount -Credential $credential

$Session = New-PSSession -ConfigurationName Microsoft.Exchange `
    -ConnectionUri https://outlook.office365.com/powershell-liveid/ `
    -Credential $credential `
    -Authentication Basic `
    -AllowRedirection

Import-PSSession $Session

# Variable to receive the codes of the Power BI workspaces
$Workspace = Get-PowerBIWorkspace -Scope Organization -Include All

# Variable to save the final result
$Result = @()

# Loop for each workspace, get the data from the column below
ForEach ($workspace in $Workspace)
    {
        # Create a new object that would store the information
        $ItemResult = New-Object System.Object
        $ItemResult | Add-Member -type NoteProperty -name WorkspaceID -value $workspace.Id
        $ItemResult | Add-Member -type NoteProperty -name WorkspaceName -value $workspace.Name
        $ItemResult | Add-Member -type NoteProperty -name IsReadOnly -value $workspace.IsReadOnly
        $ItemResult | Add-Member -type NoteProperty -name IsOnDedicatedCapacity -value $workspace.IsOnDedicatedCapacity
        $ItemResult | Add-Member -type NoteProperty -name CapacityId -value $workspace.CapacityId
        $ItemResult | Add-Member -type NoteProperty -name Description -value $workspace.Description
        $ItemResult | Add-Member -type NoteProperty -name WorkspaceType -value $workspace.Type
        $ItemResult | Add-Member -type NoteProperty -name State -value $workspace.State
        $ItemResult | Add-Member -type NoteProperty -name IsOrphaned -value $workspace.IsOrphaned

        # Put the item result and append it to the result object
        $Result +=$ItemResult
    }

# To check the final result on the screen
#$Result | Select WorkspaceID, WorkspaceName, IsReadOnly, IsOnDedicatedCapacity, CapacityId, Description, WorkspaceType, State, IsOrphaned, Users, Reports, Dashboards, Datasets, Dataflows, Workbooks | format-table -auto -wrap | Out-String      

# Defines the directory and name of the file to be exported to the CSV file
$Dir = "YOUR_DIR\WORKSPACE_CSV.csv"

# Exports the result to the CSV file in the directory informed above
$Result | Export-Csv $Dir -NoTypeInformation -Encoding UTF8

# Disconnects from the session
Remove-PSSession $Session

# Disconnects from PowerBI service
Disconnect-PowerBIServiceAccount

For the second script, obtains the data using the module “Get-PowerBIWorkspace”, and its function is to return a list of Power BI workspaces, all documentation can be found in the link below as well. And it is worth noting that the workspace is the maximum hierarchy and then we have datasets, then datasources, etc. So to get a dataset, we need to know which workspace it belongs to.

To get a datasource, we need to know which workspace it belongs to and also which dataset it pertains to, and so on.

Get-PowerBIDataset (MicrosoftPowerBIMgmt.Data)

# To execute the script without agreeing with the execution policy
Set-ExecutionPolicy Bypass -Scope Process

# Import the Power BI admin module
Import-Module MicrosoftPowerBIMgmt.Admin

# Your email to connect to the Power BI service
$username = "Your-Email" 

# Your password to connect to the PowerBI service
$password = "Your-Password" | ConvertTo-SecureString -asPlainText -Force

# Run the credential according to the login and password above
$credential = New-Object System.Management.Automation.PSCredential($username, $password)

# Connect to the Power BI service using credentials
Connect-PowerBIServiceAccount -Credential $credential

$Session = New-PSSession -ConfigurationName Microsoft.Exchange `
    -ConnectionUri https://outlook.office365.com/powershell-liveid/ `
    -Credential $credential `
    -Authentication Basic `
    -AllowRedirection

Import-PSSession $Session

# Variable to receive the codes of the Power BI workspaces
$Workspace = Get-PowerBIWorkspace -Scope Organization -Include All

# Variable to save the final result
$Result = @()

# Loop for each workspace and for each dataset, get the data from the column below
$DataSets =
ForEach ($workspace in $Workspace)
    {
    Write-Host $workspace.Name
    ForEach ($dataset in (Get-PowerBIDataset -Scope Organization -WorkspaceId $workspace.Id))
        {
        [pscustomobject]@{
            WorkspaceID = $workspace.Id
            DatasetID = $dataset.Id
            DatasetName = $dataset.Name
            ConfiguredBy = $dataset.ConfiguredBy
            DefaultRetentionPolicy = $dataset.DefaultRetentionPolicy
            AddRowsApiEnabled = $dataset.AddRowsApiEnabled
            Tables = $dataset.Tables
            WebUrl = $dataset.WebUrl
            Relationships = $dataset.Relationships
            Datasources = $dataset.Datasources
            DefaultMode = $dataset.DefaultMode
            IsRefreshable = $dataset.IsRefreshable
            IsEffectiveIdentityRequired = $dataset.IsEffectiveIdentityRequired
            IsEffectiveIdentityRolesRequired = $dataset.IsEffectiveIdentityRolesRequired
            IsOnPremGatewayRequired = $dataset.IsOnPremGatewayRequired
            TargetStorageMode = $dataset.TargetStorageMode
            ActualStorage = $dataset.ActualStorage
            CreatedDate = $dataset.CreatedDate
            ContentProviderType = $dataset.ContentProviderType
            }
        }
    }

# Defines the directory and name of the file to be exported to the CSV file
$Dir = "YOUR_DIR\DATASET_CSV.csv"

# Exports the result to the CSV file in the directory informed above
$Result | Export-Csv $Dir -NoTypeInformation -Encoding UTF8

# Disconnects from the session
Remove-PSSession $Session

# Disconnects from PowerBI service
Disconnect-PowerBIServiceAccount

In this third script, we will access the workspace and dataset using the ClientID, and this ClientID is obtained through an application created in Azure according to the link below, the process of this script is the same, but in different steps:

Create an Azure AD app & service principal in the portal - Microsoft identity platform

# To execute the script without agreeing with the execution policy
Set-ExecutionPolicy Bypass -Scope Process

# Import the Power BI admin module
Import-Module MicrosoftPowerBIMgmt.Admin

# Your email to connect to the Power BI service
$username = "Your-Email" 

# Your password to connect to the PowerBI service
$password = "Your-Password" | ConvertTo-SecureString -asPlainText -Force

# Run the credential according to the login and password above
$credential = New-Object System.Management.Automation.PSCredential($username, $password)

# Connect to the Power BI service using credentials
Connect-PowerBIServiceAccount -Credential $credential

# Variable to receive the codes of the Power BI workspaces
$Workspace = Get-PowerBIWorkspace -Scope Organization -Include All

# Defines the directory and name of the file to be exported to the CSV file
$Dir = "YOUR_DIR\DATASOURCE_CSV.csv"

# Client ID obtained by creating an application in Azure
$clientId = "Your-ClientID"

# Your email to connect to the Power BI service
$username = "Your-Email" 

# Your password to connect to the PowerBI service
$password = "Your-Password"

$body = @{
    "resource" = “https://analysis.windows.net/powerbi/api";
    "client_id" = $clientId;
    "grant_type" = "password";
    "username" = $pbiUsername;
    "password" = $pbiPassword;
    "scope" = "openid"
}
$authResponse = Invoke-RestMethod -Uri $authUrl –Method POST -Body $body

# Loop for each workspace and for each dataset, get the data from the column below
Remove-Item $ExportFile -Force -ErrorAction SilentlyContinue
foreach($workspace in $Workspaces)
{

    # Variable to receive Power BI datasets
    $DataSets = Get-PowerBIDataset -WorkspaceId $workspace.Id #| where {$_.isRefreshable -eq $true}
    foreach($dataset in $DataSets)
    {
        # Variable of the dataset ID that will be placed in the API URL
        $DatasetID = $dataset.Id

        # URL that will be used for data extraction, with the DatasetID variable
        $restURL = "https://api.powerbi.com/v1.0/myorg/datasets/$DatasetID/datasources"

        $headers = @{
            "Content-Type" = "application/json";
            "Authorization" = $authResponse.token_type + " " + $authResponse.access_token
        }

        # Receive records via URL
        $Results = Invoke-PowerBIRestMethod -Url $restURL -Method Get | ConvertFrom-Json
        foreach($result in $Results.value)
        {
            $errorDetails = $result.serviceExceptionJson | ConvertFrom-Json -ErrorAction SilentlyContinue
            $ItemResult = New-Object psobject
            $ItemResult | Add-Member -Name "WorkspaceID"       -Value $workspace.Id                     -MemberType NoteProperty
            $ItemResult | Add-Member -Name "DatasetID"         -Value $dataset.Id                       -MemberType NoteProperty
            $ItemResult | Add-Member -Name "connectionDetails" -Value $result.connectionDetails         -MemberType NoteProperty
            $ItemResult | Add-Member -Name "connectionString"  -Value $result.connectionString          -MemberType NoteProperty
            $ItemResult | Add-Member -Name "datasourceId"      -Value $result.datasourceId              -MemberType NoteProperty
            $ItemResult | Add-Member -Name "datasourceType"    -Value $result.datasourceType            -MemberType NoteProperty
            $ItemResult | Add-Member -Name "gatewayId"         -Value $result.gatewayId                 -MemberType NoteProperty
            $ItemResult | Add-Member -Name "name"              -Value $result.name                      -MemberType NoteProperty
            $ItemResult | Add-Member -Name "database"          -Value $result.database                  -MemberType NoteProperty
            $ItemResult | Add-Member -Name "server"            -Value $result.server                    -MemberType NoteProperty
            $ItemResult | Add-Member -Name "url"               -Value $result.url                       -MemberType NoteProperty
            $ItemResult | Add-Member -Name "errorDescription"  -Value $errorDetails.errorDescription    -MemberType NoteProperty
            $ItemResult | Export-Csv -Path $Dir -Append -NoTypeInformation -Encoding UTF8
        }
    }
}   

# Disconnects from PowerBI service
Disconnect-PowerBIServiceAccount

Thas it! We can now obtain Power BI service data in a variety of ways, through PowerShell using Cmdlets commands.

The script for this post can be found on my github (@guimatheus92).

guimatheus92/Get-data-from-Power-BI-service-with-PowerShell-Cmdlets: Get date from Power BI service and be able to make a decision in the BI area (github.com)