Forum Discussion
Power BI REST API - why does my cloud connector call fail with 'BadRequest'?
I have a Power BI report that's been published into the online service. It works fine. I now want to automatically extract data from a matrix within the report.
I have created a flow in Power Automate to do this: I copied the DAX behind the matrix from Power BI's Performance Analyzer, and pasted it into a variable within my flow. I then created a task to use the Power BI REST API to run a query against the report's dataset, setting my variable as the query text.
However, the task gives me this error when I run it:
Cloud connector call failed with status code 'BadRequest'.
Does anyone know why this might be happening and how I can fix it please?
Hi SRJPBI,
Thank you for sharing the details and the updated DAX query.
Based on the official Microsoft documentation, the Execute Queries REST API requires a valid DAX query that returns a table, typically using a single EVALUATE statement. The query pattern you’re using (DEFINE / VAR with one EVALUATE) is valid DAX syntax.When using this API from Power Automate, a 400 BadRequest is commonly caused by request‑formatting issues rather than the DAX logic itself:
- The DAX query is sent inside a JSON request body, so any double quotes inside the DAX (for example "IsGrandTotalRowTotal") must be escaped (e.g., \"IsGrandTotalRowTotal\") to keep the JSON valid.
- Only the DAX query should be passed to the API; comments such as // DAX Query should be removed.
Ensure the request body follows the documented format:
{ "queries": [ { "query": "<DAX query text>" } ] }In addition, please verify the documented prerequisites:
- The Dataset Execute Queries REST API tenant setting is enabled.
- The calling user has Read + Build permissions on the dataset.
For troubleshooting, you can also test with a simpler SUMMARIZECOLUMNS query to confirm the API call succeeds before adding more complex logic.
For your reference: Datasets - Execute Queries - REST API (Power BI Power BI REST APIs) | Microsoft Learn
Best regards,
Ganesh Singamshetty
7 Replies
Hi SRJPBI,
Thank you for posting your query in the Microsoft Fabric Community Forum, and thanks to Kagiyama_yutaka for sharing valuable insights.
Could you please confirm if your query has been resolved by the provided solutions? This would be helpful for other members who may encounter similar issues.
Thank you for being part of the Microsoft Fabric Community.
I think the BadReq comes from the visual DAX dragging calc‑lineage the API can’t read,
so keep only the one EVALUATE table that returns rows—if it still breaks, run that line in DAX Studio n see what op won’t fold.This doesn't seem to fix the issue. Here is my original code:
// DAX QueryDEFINEVAR __DS0Core =SUMMARIZECOLUMNS(ROLLUPADDISSUBTOTAL('Table'[Col1], "IsGrandTotalRowTotal"),"SumCol2", CALCULATE(SUM('Table'[Col2])),"SumCol3", CALCULATE(SUM('Table'[Col3])))VAR __DS0PrimaryWindowed =TOPN(502, __DS0Core, [IsGrandTotalRowTotal], 0, 'Table'[Col1], 1)EVALUATE__DS0PrimaryWindowedORDER BY[IsGrandTotalRowTotal] DESC, 'Table'[Col1]And here is what I changed it to:
// DAX QueryEVALUATETOPN(502, SUMMARIZECOLUMNS(ROLLUPADDISSUBTOTAL('Table'[Col1], "IsGrandTotalRowTotal"),"SumCol2", CALCULATE(SUM('Table'[Col2])),"SumCol3", CALCULATE(SUM('Table'[Col3]))), [IsGrandTotalRowTotal], 0, 'Table'[Col1], 1)ORDER BY[IsGrandTotalRowTotal] DESC, 'Table'[Col1]Both versions return the same error.
thanks for the update — the rewrite isn’t the issue. the query still has visual-only stuff the API can’t read: the subtotal op, the auto-added lineage/total column, and the ORDER BY. executequeries only takes a plain table expression, so those bits have to go.
Hi SRJPBI,
Thank you for sharing the details and the updated DAX query.
Based on the official Microsoft documentation, the Execute Queries REST API requires a valid DAX query that returns a table, typically using a single EVALUATE statement. The query pattern you’re using (DEFINE / VAR with one EVALUATE) is valid DAX syntax.When using this API from Power Automate, a 400 BadRequest is commonly caused by request‑formatting issues rather than the DAX logic itself:
- The DAX query is sent inside a JSON request body, so any double quotes inside the DAX (for example "IsGrandTotalRowTotal") must be escaped (e.g., \"IsGrandTotalRowTotal\") to keep the JSON valid.
- Only the DAX query should be passed to the API; comments such as // DAX Query should be removed.
Ensure the request body follows the documented format:
{ "queries": [ { "query": "<DAX query text>" } ] }In addition, please verify the documented prerequisites:
- The Dataset Execute Queries REST API tenant setting is enabled.
- The calling user has Read + Build permissions on the dataset.
For troubleshooting, you can also test with a simpler SUMMARIZECOLUMNS query to confirm the API call succeeds before adding more complex logic.
For your reference: Datasets - Execute Queries - REST API (Power BI Power BI REST APIs) | Microsoft Learn
Best regards,
Ganesh SingamshettyHello SRJPBI,
We hope you're doing well. Could you please confirm whether your issue has been resolved or if you're still facing challenges? Your update will be valuable to the community and may assist others with similar concerns.
Thank you.
Hello SRJPBI,
Could you please confirm if your query has been resolved by the provided solutions? This would be helpful for other members who may encounter similar issues.
Thank you for being part of the Microsoft Fabric Community.
