Looking for the Power BI API for MQuery

[SBrown_Murphy]

When administering a Power BI enviorment were the reports source their data from on-prem SQL databases I've found it is hard match a problem SQL query I see in the database with the symantic model that is running it.  I'd like to use a Power BI API to pull the MQuery's from all the symantic models in all the workspaces on the tenant.  I've done this using other APIs to create SQL inventory tables of the workspaces, datasets, and other things.  I'd like to do the same with the MQuery's now.

The only way I was able to find an API call that might work was using an AI search, I found this: 

"https://api.powerbi.com/v1.0/myorg/groups/{workspace_id}/datasets/{dataset_id}/queries"

Running it just gives this error "No HTTP resource was found that matches the request URI"

My questions are, do I have the right API URL?  Is there a way to do this?  If this is not the right API what is the right one?   

[lbendlin]

GitHub - migueesc123/PowerBIRESTAPI: A Microsoft Power BI Data Connector or Power Query Connector fo...

[SBrown_Murphy]

Thank you for this suggestion but this is not working for me either. It looks like there is a function that is supposed to return the DAX Query but it errors out too. 'https://api.powerbi.com/v1.0/myorg/datasets/{dataset_ID}/executeQueries' (400): Bad Request I tried calling that API URL from the PowerShell script and got the same error. What I'm really looking for is an API URL that will work so it can be added to my PowerShell scripts.

[lbendlin · Accepted solution]

How do you plan to authenticate?  Did you register an app in AD with the required scope?

$requestBody = @"
{ "queries": [{ "query": "EVALUATE TOPN(10,Dates,[Date])"}]
 ,"serializerSettings":{"includeNulls":true}
}
"@
Login-PowerBI
$requestUrl = "datasets/xxx/executeQueries"
$result = Invoke-PowerBIRestMethod -Method Post -Url $requestUrl -Body $requestBody
$parsed = $result | ConvertFrom-Json

Here is a script with manual authentication. 

[SBrown_Murphy]

"queries": [{ "query": "EVALUATE TOPN(10,Dates,[Date])"}]

What is this sction of the script for?

[lbendlin]

That's an example of a DAX query.  In your case you could use

EVALUATE INFO.PARTITIONS()

If you want all M Queries you would have to repeat the process with INFO.EXPRESSIONS()

Alternatively you could treat your semantic model as a SSAS endpoint and run the DMV queries directly.

Invoke-ASCmd -Server "powerbi://api.powerbi.com/v1.0/myorg/<workspace>" -Database "<semantic model>" -Query "select * from `$SYSTEM.TMSCHEMA_PARTITIONS" | Out-File -FilePath c:\users\xxx\Downloads\dmv.xml

[SBrown_Murphy]

In the scripts I've written up to this point I've used this to connect. 

Connect-PowerBIServiceAccount -ServicePrincipal -TenantId $TenantId -Credential $PbiCredential

I have a service pricible that has Admin rights (the same as I have).  This makes it easy to automate the script to run from the SQL server were the data is loaded.