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
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.
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.
Thanks , is there a step I can add in power automate to fix this please
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"
}
]
}
show a sample for
@outputs('Run_a_query_against_a_dataset')?['body/firstTableRows']
can you show me how to do this in the code as I am not understanding what is required , thankyou
treat the square brackets as part of the field name
so instead of (for example)
variables('Column1')
use
variables('[Column1]')
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
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?