Get dataset/report content via PowerShell

Anonymous — Wed, 26 Feb 2020 07:23:31 GMT

Scenario/goal: Get content (a tile value and a table) from a PowerBI report via PowerShell.

Is it possible to query for a report or dataset`s content via the API, specifically using either the Invoke-PowerBIRestMethod or the generic Invoke-RestMethod in PowerShell?

My initial testing using the MicrosoftPowerBIMgmt PowerShell module:

Connect-PowerBIServiceAccount -Tenant $tenantId -Credential $AppCredential -ServicePrincipal

$Workspace = Get-PowerBIWorkspace -Name 'Test'

$Report = Get-PowerBIReport -Name 'Test Data Report' -Workspace $Workspace

$Url = "https://api.powerbi.com/v1.0/myorg/reports/$($Report.Id.Guid)/pages"

Invoke-PowerBIRestMethod -Url $Url -Method Get

However, this only returns information about the reports pages – not the actual content/data.

Next, I tried the Get-PowerBITable cmdlet, which according to the documentation Retrieves a list of Power BI tables in the dataset that match the specified search criteria.

However, that was also not very successful:

Get-PowerBITable -DatasetId $Dataset.Id -WorkspaceId $Workspace.Id

Get-PowerBITable : Operation returned an invalid status code 'NotFound'

Both $DataSet and $Workspace is valid objects.

Looking at the dataset in the portal, there is a connection string looking like this:

powerbi://api.powerbi.com/v1.0/myorg/Test;initial catalog=Test Data

Maybe this can also be consumed somehow via PowerShell?

Any pointers in the right direction to get the actual data from a dataset or report using PowerShell would be highly appreciated.

Re: Get dataset/report content via PowerShell

JirkaZ — Mon, 02 Mar 2020 13:39:43 GMT

@Anonymous the APIs nor PS cmdlets are not there to extract data from Power BI. In your case I'd utilize reports subscriptions.

Re: Get dataset/report content via PowerShell

Anonymous — Mon, 02 Mar 2020 13:42:38 GMT

We eventually found the correct approach to achieve the goal in the mentioned scenario.

I wrote an article about the experience and how we solved it here.

topic Get dataset/report content via PowerShell in Developer

Get dataset/report content via PowerShell

Re: Get dataset/report content via PowerShell

Re: Get dataset/report content via PowerShell