Syntax for referencing Power BI data when adding a row to an excel table in Power Automate

[linriss_nr]

Hello Power BI Community - I would appreciate your help on a problem that I'm struggling with.

I'm trying to set up a scheduled flow in Power Automate to query Power BI for some summary data once per week, and add that data as new rows in an existing excel table.

I'm experimenting at the moment, and have managed to get to the point where I can run a query against a Power BI dataset, and iterate through the query results using "Apply to each" on the output "First table rows".

For each row in the Power BI query, I can add a new row to the excel table, and enter a value for each column that I've just typed directly in to the flow.

My problem is that I can't work out the syntax for an expression to get the data from each row of the power bi query in order to put it into Excel.

From the information provided in the Microsoft Power BI Blog on May 26th 2022 I think the syntax needs to be something like the following:

outputs('Run_a_query_against_a_dataset')?['body/firstTableRows']?[variables('_RowCount')]?[‘HealthColour[Value]’]

Where:

_RowCount is the flow variable I created to allow me to increment the row number for each pass of Apply to Each
HealthColour[Value] is the name of the column in the test query.

However, I've tried lots of different variations of the above expression, and every time I get an "Expression is invalid" message from Power Automate.

Can anyone point me in the direction of some documentation that explains the syntax for Power Automate expressions that relate to Power BI dataset data, or some examples of how to use it in practice?

The examples I can find relate to saving a full dataset as a new csv, which I could do, but wouldn't be a good fit for this use case.

Any assistance would be much appreciated.

[otravers · Accepted solution]

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.

[linriss_nr]

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!

[mazzurri]

Can I see what your Add a row into a table looks like? and the options?

[otravers]

---

@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/

[linriss_nr]

---

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