Forum Discussion
Syntax for referencing Power BI data when adding a row to an excel table in Power Automate
For the specific scenario of writing data from a Power BI dataset into an Excel spreadsheet, I probably wouldn't bother going via Power Automate and would use the dataset as a data source in Excel:
https://docs.microsoft.com/en-us/power-bi/collaborate-share/service-connect-excel-power-bi-datasets
In case you can't/don't want to do that, I was able to get the DAX query's data into Excel by parsing the JSON output of the dataset query. I did this twice in a row to first get the entire body of the response, then just the results. There's probably a more efficient way to do it but this is just a quick proof of concept.
Many thanks otravers , the Parse JSON action solved it for me and made the source data columns available as dynamic content in the apply to each step.
In my case, when I applied Parse JSON to the First Table Rows object, I didn't then need to apply a second Parse JSON step - I'm not sure why, but I wonder whether it has anything to do with the sample data I used to generate the Parse JSON step? (see below)
For anybody else struggling with the same thing, I found this webpage useful, which demonstrates the process of grabbing some sample JSON data from a previous run of your flow to allow you to use the Generate From Sample feature to automatically configure the Parse JSON action.
How to use Parse JSON action in Power Automate - Microsoft Tech Community
As a non-JSON expert, this was very handy.
Thanks again for taking the time to respond otravers , after a day of frustration, you got me up and running in about 10 minutes!
linriss_nr wrote:In my case, when I applied Parse JSON to the First Table Rows object, I didn't then need to apply a second Parse JSON step - I'm not sure why, but I wonder whether it has anything to do with the sample data I used to generate the Parse JSON step?
That's because I applied Parse JSON to the request's Body, not to its First table rows. Your version is one step shorter if you don't care about the meta datafound in the response headers.
Glad I could help! You motivated me to write this blog post about it:
https://www.oliviertravers.com/how-dax-query-results-power-bi-no-code/
otravers wrote:That's because I applied Parse JSON to the request's Body, not to its First table rows. Your version is one step shorter if you don't care about the meta datafound in the response headers.
Thanks for clearing that up - makes sense.
Great blog post - I feel like this is definitely an area that would benefit from some more expert content out there in the community.