Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Enhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.

Reply
SteWi
Frequent Visitor

Power BI API empty JSON result set

Hi there,

 

The result of my API call returns empty JSON sets means I receive the expected amount of sets (rows) but none contains data.

This issue only appears for the ActivityEvent cmdlet when I issue the following commands/script (sorry, indenting does not work here):

 

## ########################################################################
## Power BI Logon
## ########################################################################

$CredentialUser = "MASKED"
$CredentialPassword = ConvertTo-SecureString "MASKED" -asplaintext -force
$Credential = new-object -typename System.Management.Automation.PSCredential -argumentlist $CredentialUser, $CredentialPassword

## Connect-PowerBIServiceAccount
Connect-PowerBIServiceAccount -Credential $Credential

## ########################################################################
## Set Constants
## ########################################################################

$SqlServer = "MASKED"
$SqlDatabase = "MASKED"

$Days = 1 ## Last X days for delta load
$DaysOffset = -1
$DateNow = Get-Date

## ########################################################################
## Empty SQL Server Staging Area Table
## ########################################################################

Invoke-Sqlcmd -ServerInstance $SqlServer -Database $SqlDatabase -Query "TRUNCATE TABLE [SA].[tb_powerbi_service_event_all]"

## ########################################################################
## Load Activity Events
## ########################################################################

$Date = $DateNow.Date.AddDays($DaysOffset)

For($i = 1; $i -le $Days; $i++)
{
$ActivityEvents = Get-PowerBIActivityEvent -StartDateTime $Date.ToString("yyyy-MM-ddT00:00:00.000") -EndDateTime $Date.ToString("yyyy-MM-ddT23:59:59.999") | ConvertFrom-Json
if ($ActivityEvents.Count -gt 0) 
{
$ActivityEvents | foreach {
$SqlQuery = `
"
SET QUOTED_IDENTIFIER OFF

INSERT INTO [SA].[tb_powerbi_service_event_all] 
(
[event_id]
, [event_name]
, [activity_id]
, [activity_name]
, [user_key]
, [user_id]
, [workspace_id]
, [workspace_name]
, [capacity_id]
, [capacity_name]
, [tenant_id]
, [flag_success]
, [created_datetime]
, [request_id]

, [load_datetime]

)
VALUES 
(
""" + $_.Id + """
, """ + $_.Operation + """
, """ + $_.ActivityId + """
, """ + $_.Activity + """
, """ + $_.UserKey + """
, """ + $_.UserId + """
, """ + $_.WorkspaceId + """
, """ + $_.WorkspaceName + """
, """ + $_.CapacityId + """
, """ + $_.CapacityName + """
, """ + $_.OrganizationId + """
, """ + $_.IsSuccess + """
, """ + $_.CreationTime + """
, """ + $_.RequestId + """

, GETDATE()
)

SET QUOTED_IDENTIFIER ON
"
Invoke-Sqlcmd -ServerInstance $SqlServer -Database $SqlDatabase -Query $SqlQuery -ErrorAction SilentlyContinue
}
}
$Date = $Date.AddDays(-1)
}

## ########################################################################
## Logoff from Power BI
## ########################################################################

Disconnect-PowerBIServiceAccount

 

 

For others like Capacity, Workspace or specifically filtered ActivityEvents like ViewReport it works fine.

The account I use is Power BI Service Admin and of type windows service account in AD and AAD.

With my personal account it also works fine when I execute the script.

All my scripts are executed with Windows Task Scheduler while all scripts execute fine and use all the same structure only the above script returns empty result sets.

 

It's also interesting when I execute the above script manually in PowerShell (not via Run Job in Task Scheduler) I receive data as expected.

 

Someone has an idea how to move on with troubleshooting this?

For me it does not make sense that all other scripts using the same settings/template just using different CMDlets (they also need Power BI Service Admin account) work but only this one not and the manual execution works as well. Mmmh.

 

Regards,
SteWi

1 ACCEPTED SOLUTION
SteWi
Frequent Visitor

Oh my, I got it. This is the only script where I did not remove the following at the start of the script (and I did not paste it into my post). It turns out when the script contains import-module command the json sets are empty.

Import-Module MicrosoftPowerBIMgmt
Import-Module SqlServer

 

After removing the 2 lines it works now.

View solution in original post

3 REPLIES 3
SteWi
Frequent Visitor

Oh my, I got it. This is the only script where I did not remove the following at the start of the script (and I did not paste it into my post). It turns out when the script contains import-module command the json sets are empty.

Import-Module MicrosoftPowerBIMgmt
Import-Module SqlServer

 

After removing the 2 lines it works now.

lbendlin
Super User
Super User

There are a couple of API calls that behave like this. Read up on Value.MetaData and manual exception handling.

 

As to the "it does not make sense"  part. You are absolutely right.

Hey,

thanks for your answer. Do you have a code snippet based on my script what you mean with Value.MetaData or a helping link?

Something like this $ActivityEvents[1].Id.Metadata does not work. There is no MetaData function I could find during my search.

Thanks

Helpful resources

Announcements
July 2025 community update carousel

Fabric Community Update - July 2025

Find out what's new and trending in the Fabric community.

July PBI25 Carousel

Power BI Monthly Update - July 2025

Check out the July 2025 Power BI update to learn about new features.