Unanswered

PBI run a query against a dataset issue when using respond to Power App flow , output not in a correct json format so power apps not getting any data

(0) Share

Report

Posted on by JD_26

102

I have created a flow which when Power apps has button pressed , I wanted to bring in some data from a powerbi table and save that data as a collection using this script

Set(crm_skeds,crm_skeds_aged.Run().skeds_aged);
ForAll(Table(ParseJSON(crm_skeds)),Collect(collData,{Code: ThisRecord.Value.Code}))

My flow is reading the data from pbi fine , but it is adding [] to the column names , which I think is one of the issues

Code to read in data is

EVALUATE
SELECTCOLUMNS(
TOPN(1000, crm),
"carrier_code", crm[carrier_code],
"region", crm[region],
"certified_date", crm[certified_date],
"received_date", crm[received_date]
)

After reading in the code , I added an initialize variable

{

"type": "InitializeVariable",

"inputs": {

"variables": [

{

"name": "vrSkeds",

"type": "array",

"value": "@outputs('Run_a_query_against_a_dataset')?['body/firstTableRows']"

}

]

"runAfter": {

"Run_a_query_against_a_dataset": [

"Succeeded"

]

}

and the respond to a power app or flow is

{

"type": "Response",

"kind": "Http",

"inputs": {

"statusCode": 200,

"body": {

"skeds_aged": "@{variables('vrSkeds')}"

}

"runAfter": {

"Initialize_variable": [

"Succeeded"

]

}

Categories:

Building flows

I have the same question (0)

All responses (10)

Answers (0)

lbendlin 8,479 Super User 2025 Season 2 on at

Like (0)

Report
My flow is reading the data from pbi fine , but it is adding [] to the column names , which I think is one of the issues

How is that an issue? That's how it is designed.

Where are you getting stuck?

Was this reply helpful? Yes No
JD_26 102 on at

Like (0)

Report

I am getting stuck on the bringing data back to power apps , in the last step , as I believe it is no longer in a correct json format so is not being read , i did see some info saying add a replace [] for the column names to get it in a correct readable json format

Was this reply helpful? Yes No
lbendlin 8,479 Super User 2025 Season 2 on at

Like (0)

Report

treat the square brackets as part of the field name

so instead of (for example)

variables('Column1')

use

variables('[Column1]')

Was this reply helpful? Yes No
JD_26 102 on at

Like (0)

Report

can you show me how to do this in the code as I am not understanding what is required , thankyou

Was this reply helpful? Yes No
lbendlin 8,479 Super User 2025 Season 2 on at

Like (0)

Report

show a sample for

@outputs('Run_a_query_against_a_dataset')?['body/firstTableRows']

Was this reply helpful? Yes No
JD_26 102 on at

Like (0)

Report
The output is like this
{
"skeds_aged": "[{\"[carrier_code]\":\"DD\",\"[region]\":\"SEA\",\"[certified_date]\":\"2024-05-24T00:00:00\",\"[received_date]\":\"2024-05-24T00:00:00\"},{\"[carrier_code]\":\"8B\",\"[region]\":\"SEA\",\"[certified_date]\":\"2024-05-24T00:00:00\",\"[received_date]\":\"2024-05-24T00:00:00\"}]"
}
When I run this script in power apps I do get the same output , If use crm_skeds , but I need to parse the json so I can get it saved in a collection , I think I need to remove those brackets like [carrier_code]

Was this reply helpful? Yes No
lbendlin 8,479 Super User 2025 Season 2 on at

Like (0)

Report
Your output is nearly usable. The issue is not the square brackets in the field names, but the extra double quotes around the array.

{ "skeds_aged": [ { "[carrier_code]": "DD", "[region]": "SEA", "[certified_date]": "2024-05-24T00:00:00", "[received_date]": "2024-05-24T00:00:00" }, { "[carrier_code]": "8B", "[region]": "SEA", "[certified_date]": "2024-05-24T00:00:00", "[received_date]": "2024-05-24T00:00:00" } ] }

Was this reply helpful? Yes No
JD_26 102 on at

Like (0)

Report

Thanks , is there a step I can add in power automate to fix this please

Was this reply helpful? Yes No
lbendlin 8,479 Super User 2025 Season 2 on at

Like (0)

Report

yes, you can run a select or compose.

But I am surprised that you even need to do that. Usually the FirstTableRows comes over as a ready-to use array.

Was this reply helpful? Yes No
JD_26 102 on at

Like (0)

Report

If I use first table rows , it is like
[
"XK,EUR,2024-05-29T00:00:00\r",
""
]
i can find a way to remove the \r
I tried this but it never worked , if(empty(replace(split(item(), ',')[1], '\r', '')), null, replace(split(item(), ',')[1], '\r', '')) , I am trying to bring in data from powerbi and add or append data in a sharepoint list , based on code , if code exist append , if not add.

Was this reply helpful? Yes No