Get dataset list with PowerShell

[v-lili6-msft]

Scenario:

We can use PowerShell to get many contents from Power BI Service, such as workspace list, dataset list, report list and so on. In addition, if we combine PowerShell and Rest API, we can get the above content more simply. For more information, please refer to the links below.
Working with PowerShell in Power BI | Microsoft Power BI Blog | Microsoft Power BI

Power BI REST APIs - Power BI REST API | Microsoft Docs

Preparation:

1.Run PowerShell ISE

Firstly, start PowerShell ISE as an administrator. The Windows PowerShell Integrated Scripting Environment (ISE) is the host application for Windows PowerShell. In ISE, you can run commands and write, test, and debug scripts in a single Windows-based graphical user interface.

2.Install module.

Secondly, In order to manage the connection to Power BI Service, we need to install “MicrosoftPowerBIMgmt”. We can use the visual interface of PowerShell ISE to add this module, or use the command to add. Please refer to the link.

Power BI Cmdlets reference | Microsoft Docs

Now we can start writing the first Power BI PowerShell script!

Operations:

1. Get the list of workspaces and their corresponding datasets on Power BI Service.

Connect-PowerBIServiceAccount

$Workspace = Get-PowerBIWorkspace –All

$DataSets =

   ForEach ($workspace in $Workspace)

    {

    Write-Host $workspace.Name

    ForEach ($dataset in (Get-PowerBIDataset -WorkspaceId $workspace.Id))

        {

        [pscustomobject]@{

            WorkspaceName = $Workspace.Name

            WorkspaceID = $workspace.Id

            DatasetName = $dataset.Name

            DatasetID = $dataset.Id

            }

        }

    }

   $Dir = "C:\Users\lionelch\Desktop\Sampledata\MyWorkspace.csv"

   $DataSets | Export-Csv $Dir -NoTypeInformation -Encoding UTF8

   Disconnect-PowerBIServiceAccount

Explanation of above script:

• The first step we need to do is to use the “Connect-PowerBIServiceAccount” command to connect to the Power BI Service, and then use the “Get-PowerBIWorkspace” command to get the information of the workspace on the Power BI Service and assign it to the variable “Workspaces”.

Connect-PowerBIServiceAccount

$Workspaces = Get-PowerBIWorkspace –All

• Secondly, we need to use the function “ForEach()” to traverse the parameters “$Workspaces” and “Get-PowerBIDataset -WorkspaceId $workspace.Id”, and then output the data set lists in the corresponding Workspaces.

ForEach ($workspace in $Workspaces)

    {

    ForEach ($dataset in (Get-PowerBIDataset -WorkspaceId $workspace.Id))

        {

        [pscustomobject]@{

            WorkspaceName = $Workspace.Name

            WorkspaceID = $workspace.Id

            DatasetName = $dataset.Name

            DatasetID = $dataset.Id

            }

        }

    }

• Finally, export the results of the parameter “$Datasets” to a local .csv file and Disconnect current connection.

$Dir = "C:\Users\lionelch\Desktop\Sampledata\MyWorkspace.csv"

$DataSets | Export-Csv $Dir -NoTypeInformation -Encoding UTF8

Disconnect-PowerBIServiceAccount

2. Get the lists of workspaces and data sets in PowerShell.

A RESTful API is an architectural style for an application program interface (API) that uses HTTP requests to access and use data. That data can be used to GET, PUT, POST and DELETE data types, which refers to the reading, updating, creating and deleting of operations concerning resources. For Power BI Service, Microsoft officially provides many Rest APIs for developers to use.

Connect-PowerBIServiceAccount

$Workspace = Get-PowerBIWorkspace –All

ForEach($Workspace in $Workspace)

   {

       $Url = 'https://api.powerbi.com/v1.0/myorg/groups/'+ $Workspace.Id +  '/datasets'

       Invoke-PowerBIRestMethod -Url $Url -Method Get

}

Disconnect-PowerBIServiceAccount

Explanation of script above:

• Here we use the “Get Dataset API”. First, we still need to connect to the Power BI Service, and then use the “Get-PowerBIWorkspace” command to get the data of the workspace and assign it to the parameter "$Workspace".

Connect-PowerBIServiceAccount

$Workspace = Get-PowerBIWorkspace –All

• Second and most important, we need to use the “Invoke-PowerBIRestMethod” command to access the API and return JSON information. Please refer to the link.

Invoke-PowerBIRestMethod (MicrosoftPowerBIMgmt.Profile) | Microsoft Docs

$Url = 'https://api.powerbi.com/v1.0/myorg/groups/'+ $Workspace.Id + '/datasets'

Invoke-PowerBIRestMethod -Url $Url -Method Get

Result:

Summary:

The above content introduces how to use PowerShell to connect to Power BI Service, how to get the content in Power BI Service through command codes, and how to use Rest API in PowerShell. These are just basic content. In fact, PowerShell has more abundant features, including the realization of report refresh, log monitoring and realization of certain automatic features. Last but not least, Power BI REST APIs have some limitations, please refer to this document for details.

Power BI REST API limitations - Power BI | Microsoft Docs

Author: Lionel Chen

Reviewer: Yuyang & Ula