Why is the Power BI REST API saving a query result as a string?

[SRJPBI]

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. The matrix looks like this:

Campaign	Mailed	Opened
AAA	238096	118801
BBB	198519	98489
CCC	195848	100218
Total	632463	317508

I've set up a flow in Power Automate using the Power BI REST API to run the query. The flow looks like this:

1. Run a query against a dataset
2. Launch Excel
3. Write to Excel worksheet
4. Save Excel

The flow runs successfully, but when I open the resulting spreadsheet it just contains this string in cell A1:

{"results":[{"tables":[{"rows":[{"Table[Campaign]":"AAA","[SumMailed]":238096.0,"[SumOpened]":118801.0},{"Table[Campaign]":"BBB","[SumMailed]":198519.0,"[SumOpened]":98489.0},{"Table[Campaign]":"CCC","[SumMailed]":195848.0,"[SumOpened]":100218.0}]}]}],"firstTableRows":[{"Table[Campaign]":"AAA","[SumMailed]":238096.0,"[SumOpened]":118801.0},{"Table[Campaign]":"BBB","[SumMailed]":198519.0,"[SumOpened]":98489.0},{"Table[Campaign]":"CCC","[SumMailed]":195848.0,"[SumOpened]":100218.0}]}

Does anyone know how I can have the query results save into a spreadsheet in tabular format please? 

If it helps, here is my DAX:

EVALUATE
TOPN(501, SUMMARIZECOLUMNS(
'Table'[Campaign],
"SumMailed", CALCULATE(SUM('Table'[Mailed])),
"SumOpened", CALCULATE(SUM('Table'[Opened]))
), 'Table'[Campaign], 1)

ORDER BY
'Table'[Campaign]

[DataTako]

As far as I know, "Show data point as a table" isn't available for all visuals in the Power BI Service, even though it is in Power BI Desktop. The Desktop and Service don't always offer the same context menu options, especially for map visuals.

Since you mentioned this used to work for the same report, I'd first check whether anything has changed recently, such as the visual type, tenant settings, or a recent Power BI Service update. If nothing has changed on your end, it could also be a regression in the Service.

As a workaround, you could use Drillthrough, a report page tooltip, or a table visual that responds to the map selection, depending on the experience you're trying to provide.

I'd be interested to hear if others can still reproduce this in the current version of the Power BI Service.

[v-achippa]

Hi @SRJPBI,

Thank you for reaching out to Microsoft Fabric Community.

Here looks like this is expected, the "Run a query against a dataset" action returns its result in JSON format, not as a table. Since your flow writes the raw response directly to excel, the entire JSON is being stored as a single string in cell A1.

• To get the data in tabular format, add a Parse JSON action after Run a query against a dataset and extract the rows first and then write those rows to excel, instead of writing the raw response directly. Once the rows are parsed and mapped to columns, the data will save in proper tabular format.

For reference, please follow the below documentation:

Datasets - Execute Queries - REST API (Power BI Power BI REST APIs) | Microsoft Learn

Thanks and regards,

Anjan Kumar Chippa

[SRJPBI]

@v-achippa Thanks for the explanation; that makes sense. However, I am using Power Automate Desktop, which I don't believe has the function Parse JSON. Is there a way to do this in the Desktop version please?

[v-achippa]

Hi @SRJPBI,

Thank you for the response and confirming that you are using power automate desktop.

• After Run a query against a dataset, add Convert JSON to custom object action to convert the API response into a custom object. After that you can access the returned data, loop through the firstTableRows and write each record to excel instead of writing the raw json response.

For reference, follow the below documentation:

Variables actions reference - Power Automate | Microsoft Learn

Thanks and regards,

Anjan Kumar Chippa