Parse JSON to collection from Data Returned from Power Automate Flow

(0) Share

Report

Posted on by MRBCODC

I am struggling to figure out where I am going wrong here.

I have data returning from a flow which I need to go in to a collection, the output from my flow and my formula is

Flow Output

{

  "biorderdata": "{\"body\":[{\"Ordr Group Delivery Group\":\"780693/1\",\"Jobsite\":\"81651998\",\"Mat Code\":10119696,\"Del number\":314500464,\"Desp Plant\":\"I800\",\"Del Dat Start \":\"2022-12-12T00:00:00\",\"Del Date End\":\"2022-12-12T00:00:00\",\"Del Time Start\":\"2022-12-12T00:00:00\",\"Del Time End\":\"1899-12-30T15:00:00\"}]}"

}

Formula

ClearCollect(
 ColSAp,
 ForAll(
 Table( ParseJSON(BILookup.Run(TextInput2).biorderdata).body),
 {
 Jobsite: Value(Value.Jobsite),
 'Mat Code': Value(Value.'Mat Code'),
 'Del number': Value(Value.'Del number'),
 'Desp Plant': Text(Value.'Desp Plant'),
 'Del Dat Start':DateValue(Value.'Del Dat Start'),
 'Del Date End': DateValue(Value.'Del Date End'),
 'Del Time Start': DateValue(Value.'Del Time Start'),
 'Del Time End': DateValue(Value.'Del Time End')
 }

 )
)

Thanks in advance for anyone who can point me in the right direction

Categories:

Building Power Apps

I have the same question (0)

All responses (6)

Answers (0)

Sundeep_Malik 6,484 on at

Like (1)

Report
Copy link

Link copied!

Hey @MRBCODC

Can you show the Power Automate steps/actions you have used? Screenshots!!

Was this reply helpful? Yes No
MRBCODC 69 on at

Like (0)

Report
Copy link

Link copied!

Hi @Sundeep_Malik

Sure here are the screenshots

Thanks

Was this reply helpful? Yes No
Sundeep_Malik 6,484 on at

Like (1)

Report
Copy link

Link copied!

@MRBCODC

I am probably sure, those extra things(slashes etc) are coming from select action.

I hope that is the issue only that you want to remove those extra things?

Run the flow once and check if Select is responsible for that.

Instead I would suggest you to take a compose action and make a json structure inside it.

Flow after parse json: (make a separate flow of possible so that you don't lose the select action)
In that compose write:

{
"YourField1": Dynamic Expression for same;
"YourField2": Dynamic Expression for same;
}

This might create an appy to each loop. If it does, declare an array variable outside the apply to each.
After that inside apply to each add action
Append to array
and pass outputs of the above compose you made.

After this,
Send the outputs of compose or the variable to the respond to app or flow action.

If this does not work, send me the data you are receiving after parse json. Hide the personal details with something else.
Let's say Jobsitenumber : 6363
Then replace it with Jobsitenumber : 123

Was this reply helpful? Yes No
MRBCODC 69 on at

Like (0)

Report
Copy link

Link copied!
Hi @Sundeep_Malik

I have managed to get it working using the response task rather than the respond to power apps.

I am having a new issue though. there may be times where the data that comes from power bi has empty fields which is causing the flow to fail.
I have tried editing the schema to add
"type": ["string", "null"]
But its still failing
[

  {

    "message": "Required properties are missing from object: CEM_Current[Delivery], CEM_Current[Delivery SOTR], CEM_Current[Delivery Item SOTR], CEM_Current[Licence Number SOTR], CEM_Current[Dispatched Quantity], CEM_Current[UOM], CEM_Current[Return Type], CEM_Current[Returned Quantity], CEM_Current[Confirmed Quantity], CEM_Current[Shipment SOTR], CEM_Current[Return Reason], CEM_Current[Ext Delivery], CEM_Current[Vendor Name], CEM_Current[Tare], CEM_Current[Gross].",

    "lineNumber": 0,

    "linePosition": 0,

    "path": "[0]",

    "value": [

      "CEM_Current[Delivery]",

      "CEM_Current[Delivery SOTR]",

      "CEM_Current[Delivery Item SOTR]",

      "CEM_Current[Licence Number SOTR]",

      "CEM_Current[Dispatched Quantity]",

      "CEM_Current[UOM]",

      "CEM_Current[Return Type]",

      "CEM_Current[Returned Quantity]",

      "CEM_Current[Confirmed Quantity]",

      "CEM_Current[Shipment SOTR]",

      "CEM_Current[Return Reason]",

      "CEM_Current[Ext Delivery]",

      "CEM_Current[Vendor Name]",

      "CEM_Current[Tare]",

      "CEM_Current[Gross]"

    ],

    "schemaId": "#/items",

    "errorType": "required",

    "childErrors": []

  }

]

Can you see where or why its failing

Thanks

Was this reply helpful? Yes No
Sundeep_Malik 6,484 on at

Like (1)

Report
Copy link

Link copied!

@MRBCODC

In the parse json action remove required part:

->

Was this reply helpful? Yes No
MRBCODC 69 on at

Like (1)

Report
Copy link

Link copied!

@Sundeep_Malik

thank you that worked appreciate your help

Was this reply helpful? Yes No