Forum Discussion
New API Endpoint with DAX Queries
I finally got this partially worked out with the help of one of the very helpful Power BI engineers. For some reason datasets in My Workspace don't seem to be getting proper build permissions, so I decided to switch to just testing datasets in my v2 workspaces.
I initally thought that I had to use the format https://api.powerbi.com/v1.0/myorg/groups/{groupId}/datasets/{datasetId} because that is what I'm accustomed to doing. This will probably change as this gets closer to GA, but for now you actually just remove the parts about the group and reference the dataset directly. For example: https://api.powerbi.com/v1.0/myorg/datasets/f388a618-2c93-41c5-8528-44c0c1129d70/executeQueries
Once I made that change, I was able to return data from a Powershell script and Postman. Still not sure about the permssions in My Workspace, but I'll likely never use that anway.
I have exactly the same kind of issue as you rjhale. Same error message.
I have also verified that the Rest API is enabled in the admin portal and that I have build access to the dataset.
$requestUrl = 'https://api.powerbi.com/v1.0/myorg/datasets/e500cd5d-4e48-4feb-80d5-33435a5c9a16/executeQueries'
$requestBody = @"
{ “queries”: [{“query”:”EVALUATE SUMMARIZECOLUMNS('Movements[countryCode]', \"Ship Count\", [shipCount])“}], “serializerSettings”:{“incudeNulls”: true}}
"@
Write-Host $requestBody
Write-Host 'Logging in'
Login-PowerBI
Write-Host 'Logged In'
$result = Invoke-PowerBIRestMethod -Method POST -Url $requestUrl -Body $requestBody
Resolve-PowerBIError -Last
Write-Host 'Invoked'
$result
$parsed = $result | ConvertFrom-Json
$parsed.results[0].tables[0].rows | Format-List
Write-Host 'Done'
I get the following response:
Logged In
Invoke-PowerBIRestMethod : One or more errors occurred.
At C:\Users\lchh\OneDrive - Bunker Holding Group\PowerShell\SampleDaxRestAPI.ps1:26 char:11
+ $result = Invoke-PowerBIRestMethod -Method POST -Url $requestUrl -Bod ...
+ ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
+ CategoryInfo : WriteError: (Microsoft.Power...werBIRestMethod:InvokePowerBIRestMethod) [Invoke-PowerBIRestMethod], AggregateException
+ FullyQualifiedErrorId : One or more errors occurred.,Microsoft.PowerBI.Commands.Profile.InvokePowerBIRestMethod
Exception : System.Net.Http.HttpRequestException: Response status code does not indicate success: 400 (Bad Request).
at System.Net.Http.HttpResponseMessage.EnsureSuccessStatusCode()
at Microsoft.PowerBI.Commands.Profile.InvokePowerBIRestMethod.<InvokeRestMethod>d__35.MoveNext()
InnerException : False
Message : Response status code does not indicate success: 400 (Bad Request).
StackTrace : at System.Net.Http.HttpResponseMessage.EnsureSuccessStatusCode()
at Microsoft.PowerBI.Commands.Profile.InvokePowerBIRestMethod.<InvokeRestMethod>d__35.MoveNext()
HelpLink :
Source : System.Net.Http
ErrorDetails :
ErrorCategory : WriteError: (Microsoft.Power...werBIRestMethod:InvokePowerBIRestMethod) [Invoke-PowerBIRestMethod], AggregateException
InvocationInfo : System.Management.Automation.InvocationInfo
ScriptStackTrace : at <ScriptBlock>, C:\Users\lchh\OneDrive - Bunker Holding Group\PowerShell\SampleDaxRestAPI.ps1: line 26
Invoked
Cannot index into a null array.
At C:\Users\lchh\OneDrive - Bunker Holding Group\PowerShell\SampleDaxRestAPI.ps1:31 char:1
+ $parsed.results[0].tables[0].rows | Format-List
+ ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
+ CategoryInfo : InvalidOperation: (:) [], RuntimeException
+ FullyQualifiedErrorId : NullArray
Done
Anonymous is there anyway that this is not rolled out to our Power BI tenants?
I finally got this partially worked out with the help of one of the very helpful Power BI engineers. For some reason datasets in My Workspace don't seem to be getting proper build permissions, so I decided to switch to just testing datasets in my v2 workspaces.
I initally thought that I had to use the format https://api.powerbi.com/v1.0/myorg/groups/{groupId}/datasets/{datasetId} because that is what I'm accustomed to doing. This will probably change as this gets closer to GA, but for now you actually just remove the parts about the group and reference the dataset directly. For example: https://api.powerbi.com/v1.0/myorg/datasets/f388a618-2c93-41c5-8528-44c0c1129d70/executeQueries
Once I made that change, I was able to return data from a Powershell script and Postman. Still not sure about the permssions in My Workspace, but I'll likely never use that anway.
Thank you so much for your response rjhale.
I'm really happy that you succeeded, because then I know there is a way.
I did go through the concerns that you describe.1) My request url is directly to the dataset + '/executeQueries'
2) My workspace is a V.2 workspace
3) I am admin of the workspace, but just to make sure, I have given myself Build permissions directly on the dataset.
Can you please provide you code as sample code, so I can copy as much as possible from working powershell code, rjhale ?