Parse JSON to collection from Data Returned from Power Automate Flow

[MRBCODC]

Hi 

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

[Sundeep_Malik]

Hey @MRBCODC 

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

[MRBCODC]

Hi @Sundeep_Malik 

Sure here are the screenshots

Thanks

[Sundeep_Malik]

@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

[MRBCODC]

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

[Sundeep_Malik]

@MRBCODC 

In the parse json action remove required part:

->

[MRBCODC]

@Sundeep_Malik 

thank you that worked appreciate your help