Run a query against a dataset action in power automate error- should return data in JSON format

[Yonas]

Hi, I'm trying to Run a query against a dataset action in Power automate by copying a DAX query form a powerBI table visual, when I run it, it is failing & raising the below error- should return data in JSON format. The query runs perfectly & returns values in DAX query in Powerbi but I'm not sure why it is failing in Power automate & why I am asked to change it to JSON format since I saw many people/blogs How to Run a Query against a Dataset Using Power Automate? - Enjoy SharePoint showing it can be run perfectly without changing to JSON. Could you please help what am I missing?

[v-agajavelly]

Hey @Yonas 

Thanks for sharing all the details, you're on the right track, as per my experiance especially when the DAX runs fine in Power BI but hits a wall in Power Automate. This happens often when the DAX query doesn’t return a proper JSON-formatted table that Power Automate expects. Even if it works in Power BI, Power Automate needs it in a clean tabular format (like using SELECTCOLUMNS or SUMMARIZECOLUMNS inside an EVALUATE).
Try wrapping your measure in something like this by bellow way.

EVALUATE
SELECTCOLUMNS({ [Your Measure] }, "Result", [Value])

Or better, structure your query to return rows and columns, that usually fixes the JSON issue.

_________________________________________________________________________________________________________________________

If this post helps, then please consider Accepting as solution to help the other members find it more quickly, don't forget to give a "Kudos" – I’d truly appreciate it!

Regards,
Akhil.