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]

Hi @Yonas ,

I’m following up to confirm that all items discussed have been addressed. If there’s anything else you need, I’d be happy to assist. Please feel free to reach out Microsoft fabric community forum.

Regards,
Akhil,

[v-agajavelly]

Hi @Yonas ,

Just circling back were you able to get the Power Automate flow working after adjusting the DAX structure? That SELECTCOLUMNS trick usually does the job, but if it’s still giving you trouble or returning unexpected output, feel free to share the query you're using. Happy to take a look and help on it.

Thanks,
Akhil.

[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.