Forum Discussion

SBrown_Murphy's avatar
SBrown_Murphy
Frequent Visitor
2 years ago
Solved

Looking for the Power BI API for MQuery

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 th...
Need Help
  • lbendlin's avatar
    lbendlin
    2 years ago

    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. 

lbendlin's avatar
lbendlin
Super User
2 years ago

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's avatar
SBrown_Murphy
Frequent Visitor
2 years ago
"queries": [{ "query": "EVALUATE TOPN(10,Dates,[Date])"}]

What is this sction of the script for?

  • lbendlin's avatar
    lbendlin
    Super User
    2 years ago

    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