Convert array of items into array of JSON objects

[autoperfect93]

Hello All,

Is need to convert an array of items into an array of objects in Power Automate.

For example the below array of items which is split into separate rows via comma (,).

I need to convert it into an array of JSON objects. Like this format shown below:

Which expression or action I need to use in order to achieve this in Power Automate?

Thanks in advance for your feedback.

[lbendlin]

Please provide sample data (with sensitive information removed) that covers your issue or question completely, in a usable format (not as a screenshot). Leave out anything not related to the issue.

[grantjenkins]

Will there always be exactly 17 items in each object? If yes, then this is how I'd approach it.

See full flow below. I'll go into each of the actions.

Compose contains your array of values. For this example I will have 4 items per object. I'm using the following data that includes your first item that we will remove "EXENAME=FXDEAL", a few empty items at the end, and the second Beta doesn't have a value.

[
    "EXENAME=FXDEAL",
    "Alpha=One",
    "Beta=Two",
    "Gamma=Three",
    "Delta=Four",
    "",
    "Alpha=1",
    "Beta=",
    "Gamma=3",
    "Delta=4",
    "",
    ""
]

Filter array uses the following expressions to clean up our data.

//From (removes the first item)
skip(outputs('Compose'), 1)

Condition (will allow us to remove any empty items)
trim(item())

Select chunks our data into chunks of 4 items at a time and the following expressions to get the names and corresponding values.

//From
chunk(body('Filter_array'), 4)

//Map
//Names
split(item()[0], '=')?[0]
split(item()[1], '=')?[0]
split(item()[2], '=')?[0]
split(item()[3], '=')?[0]

//Values
split(item()[0], '=')?[1]
split(item()[1], '=')?[1]
split(item()[2], '=')?[1]
split(item()[3], '=')?[1]

This will give us the following output from our Select.

[
  {
    "Alpha": "One",
    "Beta": "Two",
    "Gamma": "Three",
    "Delta": "Four"
  },
  {
    "Alpha": "1",
    "Beta": "",
    "Gamma": "3",
    "Delta": "4"
  }
]

[autoperfect93]

Hi @lbendlin  and @grantjenkins ,

Thank you both for reaching out.

Unfortunately the number of items in each object will vary - some will be with 17 items and some with 19 items. The reason for this is that inside the file I have different type of foreign exchange deals.

I am attaching an example file. 

Inside the example file I have 3 deals each one starts with "$DoMenu=mfnew" and ends with "$DoMenu=mfInsert".

The first row of every file contains the following text "EXENAME=FXDEAL"

The first 2 deals are with 17 items (rows) and the 3rd deal is with 19 items (rows).

[grantjenkins]

Hopefully this will get what you're after. It should work for any number of items for each object.

One thing to note is that an object can't have duplicate property names, so you can't have "$DoMenu": "mfnew" and "$DoMenu": "mfInsert". So, I remove the $ from the mfnew property name in the final output.

See full flow below. I'll go into each of the actions.

Data is a Compose that contains your data.

Select uses the following expressions to split the data by $DoMenu=mfnew, remove the first row, and split by new line.

//From
skip(split(outputs('Data'), '$DoMenu=mfnew'), 1)

//Map
split(item(), decodeUriComponent('%0A'))

Initialize variable data creates a new variable called data of type Array and sets it as a blank array using []. This will eventually contain all of our objects.

Initialize variable item creates a new variable called item of type Object and sets it as an initial object with the DoMenu item as it's only property.

{
"DoMenu": "mfnew"
}

Apply to each array uses the output from the Select so it iterates over each of the arrays.

Filter array removes any blank rows using the following expression.

trim(item())

Apply to each item uses the output from the Filter array so it iterates over each item in the current array.

Compose appends each property/value to the item variable.

addProperty(variables('item'), split(item(), '=')?[0], split(item(), '=')?[1])

Set variable item sets the item to the output from Compose.

Append to array variable appends the output from the item variable to the data variable.

Set variable item DoMenu resets the item variable to the following so it's ready for the next array.

{
"DoMenu": "mfnew"
}

Note - because we are setting values in our variables within our Apply to each loops, we should set both Apply to each array and Apply to each item to a Degree of Parallelism to 1. To do this, click on the three dots ... then Settings, turn on Concurrency Control and set the Degree of Parallelism to 1.

After running the flow, we should get the following output in the data variable. Note the DoMenu and $DoMenu property names.

[
  {
    "DoMenu": "mfnew",
    "ticket_no": "SRM521213919",
    "sectype": "FX Forward",
    "Cparty": "SK01-PAI",
    "Entity": "SI02-PN",
    "face_value": "155091",
    "ccy": "EUR",
    "ccy2": "USD",
    "contr_rate": "1.11",
    "spot_rate": "1.11",
    "fwd_rate": "0",
    "deal_dt": "24-Nov-2023",
    "mature_dt": "20-Dec-2023",
    "Dealer": "Batch",
    "Comments": "Rino",
    "analyse ": " Risk Center|",
    "$DoMenu": "mfInsert"
  },
  {
    "DoMenu": "mfnew",
    "ticket_no": "SRM521213919",
    "sectype": "FX Forward",
    "Cparty": "SK01-PAI",
    "Entity": "SI02-PN",
    "face_valu2": "12791982",
    "ccy": "USD",
    "ccy2": "EUR",
    "contr_rate": "1.0925840",
    "spot_rate": "1.0925840",
    "fwd_rate": "0",
    "deal_dt": "24-Nov-2023",
    "mature_dt": "20-Dec-2023",
    "Dealer": "Batch",
    "Comments": "",
    "analyse ": " Risk Center|",
    "$DoMenu": "mfInsert"
  },
  {
    "DoMenu": "mfnew",
    "ticket_no": "SRM521213919",
    "sectype": "Non Deliverable Forward",
    "Cparty": "SER05-FI",
    "Entity": "KIG03-PRE",
    "face_value": "58900315",
    "ccy": "JPY",
    "ccy2": "USD",
    "contr_rate": "0.006891",
    "spot_rate": "0.006891",
    "fwd_rate": "0",
    "deal_dt": "24-Nov-2023",
    "mature_dt": "20-Dec-2023",
    "Dealer": "Batch",
    "Comments": "",
    "rate_fix_date": "17-Dec-2023",
    "settlement_ccy": "USD",
    "analyse ": " Risk Center|",
    "$DoMenu": "mfInsert"
  }
]

[Matthy79 · Accepted solution]

Hello @autoperfect93 

as @grantjenkins already mentioned it is not possible to create an object array as you requested it because of the duplicate properties.

In your example you also wanted to change the data types and trim results. If this is not needed you can also try this:

From: skip(split(concat('"', join(outputs('Compose_Simple_Array'), '","'), '"'), '"$DoMenu=mfnew",'), 1)

Map: json(concat('{ "$DoMenu":"mfnew", ', replace(first(split(item(), '"$DoMenu=mfInsert"')), '=', '":"'), '"$DoMenu2":"mfInsert" }'))

If you need the additional change of types you can do this:

Here is a sample for contr_rate: if(equals(item()?['contr_rate'], null), null, float(item()?['contr_rate']))

And if you don't want those null values for some properties that doesn't exist in the input values you could do it like this (after the first step that is always the same):

Here is a whole scope to test with.

{"id":"9e358817-4c7e-4756-90ed-3fa82b95a791","brandColor":"#8C3900","connectionReferences":{"shared_teams":{"connection":{"id":"/providers/Microsoft.PowerApps/apis/shared_teams/connections/da0f4068677c48aba01f0613c77dcb5b"}},"shared_commondataserviceforapps":{"connection":{"id":"/providers/Microsoft.PowerApps/apis/shared_commondataserviceforapps/connections/62947705b1a746319ca1e59aae985afc"}}},"connectorDisplayName":"Control","icon":"data:image/svg+xml;base64,PHN2ZyB3aWR0aD0iMzIiIGhlaWdodD0iMzIiIHZlcnNpb249IjEuMSIgdmlld0JveD0iMCAwIDMyIDMyIiB4bWxucz0iaHR0cDovL3d3dy53My5vcmcvMjAwMC9zdmciPg0KIDxwYXRoIGQ9Im0wIDBoMzJ2MzJoLTMyeiIgZmlsbD0iIzhDMzkwMCIvPg0KIDxwYXRoIGQ9Im04IDEwaDE2djEyaC0xNnptMTUgMTF2LTEwaC0xNHYxMHptLTItOHY2aC0xMHYtNnptLTEgNXYtNGgtOHY0eiIgZmlsbD0iI2ZmZiIvPg0KPC9zdmc+DQo=","isTrigger":false,"operationName":"Scope_Transform_simple_array","operationDefinition":{"type":"Scope","actions":{"Compose_Simple_Array":{"type":"Compose","inputs":["EXENAME=FXDEAL","$DoMenu=mfnew","ticket_no=SRM521213919","sectype=FX Forward","Cparty=SK01-PAI","Entity=SI02-PN","face_value=155091","ccy=EUR","ccy2=USD","contr_rate=1.11","spot_rate=1.11","fwd_rate=0","deal_dt=24-Nov-2023","mature_dt=20-Dec-2023","Dealer=Batch","Comments=Rino","analyse = Risk Center|","$DoMenu=mfInsert","","$DoMenu=mfnew","ticket_no=SRM521213919","sectype=FX Forward","Cparty=SK01-PAI","Entity=SI02-PN","face_valu2=12791982","ccy=USD","ccy2=EUR","contr_rate=1.0925840","spot_rate=1.0925840","fwd_rate=0","deal_dt=24-Nov-2023","mature_dt=20-Dec-2023","Dealer=Batch","Comments=","analyse = Risk Center|","$DoMenu=mfInsert","","$DoMenu=mfnew","ticket_no=SRM521213919","sectype=Non Deliverable Forward","Cparty=SER05-FI","Entity=KIG03-PRE","face_value=58900315","ccy=JPY","ccy2=USD","contr_rate=0.006891","spot_rate=0.006891","fwd_rate=0","deal_dt=24-Nov-2023","mature_dt=20-Dec-2023","Dealer=Batch","Comments=","rate_fix_date=17-Dec-2023","settlement_ccy=USD","analyse = Risk Center|","$DoMenu=mfInsert",""],"runAfter":{},"metadata":{"operationMetadataId":"0af893cd-720f-4c88-a12e-c02187f88d95"}},"Select_solution1":{"type":"Select","inputs":{"from":"@skip(split(concat('\"', join(outputs('Compose_Simple_Array'), '\",\"'), '\"'), '\"$DoMenu=mfnew\",'), 1)","select":"@json(concat('{ \"$DoMenu\":\"mfnew\", ', replace(first(split(item(), '\"$DoMenu=mfInsert\"')), '=', '\":\"'), '\"$DoMenu2\":\"mfInsert\" }'))"},"runAfter":{"Compose_Simple_Array":["Succeeded"]},"metadata":{"operationMetadataId":"0be57ec4-1692-4a54-9281-6c17649bcac7"}},"Select_solution3_temp_array_string":{"type":"Select","inputs":{"from":"@body('Select_solution1')","select":{"Dummy":"{\n\t\"$DoMenu\": \"mfnew\",\n@{if(equals(item()?['ticket_no'],null), null, concat('\"ticket_no\": \"', item()?['ticket_no'], '\",'))}\n@{if(equals(item()?['sectype'],null), null, concat('\"sectype\": \"', item()?['sectype'], '\",'))}\n@{if(equals(item()?['Cparty'],null), null, concat('\"Cparty\": \"', item()?['Cparty'], '\",'))}\n@{if(equals(item()?['Entity'],null), null, concat('\"Entity\": \"', item()?['Entity'], '\",'))}\n@{if(equals(item()?['face_value'],null), null, concat('\"face_value\": ', item()?['face_value'], ','))}\n@{if(equals(item()?['ccy'],null), null, concat('\"ccy\": \"', item()?['ccy'], '\",'))}\n@{if(equals(item()?['ccy2'],null), null, concat('\"ccy2\": \"', item()?['ccy2'], '\",'))}\n@{if(equals(item()?['contr_rate'],null), null, concat('\"contr_rate\": ', item()?['contr_rate'], ','))}\n@{if(equals(item()?['spot_rate'],null), null, concat('\"spot_rate\": ', item()?['spot_rate'], ','))}\n@{if(equals(item()?['fwd_rate'],null), null, concat('\"fwd_rate\": ', item()?['fwd_rate'], ','))}\n@{if(equals(item()?['deal_dt'],null), null, concat('\"deal_dt\": \"', item()?['deal_dt'], '\",'))}\n@{if(equals(item()?['mature_dt'],null), null, concat('\"mature_dt\": \"', item()?['mature_dt'], '\",'))}\n@{if(equals(item()?['Dealer'],null), null, concat('\"Dealer\": \"', item()?['Dealer'], '\",'))}\n@{if(equals(item()?['Comments'],null), null, concat('\"Comments\": \"', item()?['Comments'], '\",'))}\n@{if(equals(item()?['rate_fix_date'],null), null, concat('\"rate_fix_date\": \"', item()?['rate_fix_date'], '\",'))}\n@{if(equals(item()?['settlement_ccy'],null), null, concat('\"settlement_ccy\": \"', item()?['settlement_ccy'], '\",'))}\n@{if(equals(item()?['analyse '],null), null, concat('\"analyse\": \"', item()?['analyse '], '\",'))}\n\t\"$DoMenu2\": \"mfInsert\"\n}"}},"runAfter":{"Select_solution2_from_solution1":["Succeeded"]},"metadata":{"operationMetadataId":"b3742ab1-2a43-45d5-97af-4aa5d52483bd"}},"Select_solution3_from_fast_solution1":{"type":"Select","inputs":{"from":"@body('Select_solution3_temp_array_string')","select":"@json(item()['Dummy'])"},"runAfter":{"Select_solution3_temp_array_string":["Succeeded"]},"description":"json(item()['Dummy'])","metadata":{"operationMetadataId":"19f5d315-4f33-49f4-b565-fc7f6e6d14b0"}},"Select_solution2_from_solution1":{"type":"Select","inputs":{"from":"@body('Select_solution1')","select":{"$DoMenu":"mfnew","ticket_no":"@item()?['ticket_no']","sectype":"@item()?['sectype']","Cparty":"@item()?['Cparty']","Entity":"@item()?['Entity']","face_value":"@if(equals(item()?['face_value'], null), null, float(item()?['face_value']))","ccy":"@item()?['ccy']","ccy2":"@item()?['ccy2']","contr_rate":"@if(equals(item()?['contr_rate'], null), null, float(item()?['contr_rate']))","spot_rate":"@if(equals(item()?['spot_rate'], null), null, float(item()?['spot_rate']))","fwd_rate":"@if(equals(item()?['fwd_rate'], null), null, float(item()?['fwd_rate']))","deal_dt":"@item()?['deal_dt']","mature_dt":"@item()?['mature_dt']","Dealer":"@item()?['Dealer']","Comments":"@item()?['Comments']","rate_fix_date":"@item()?['rate_fix_date']","settlement_ccy":"@item()?['settlement_ccy']","analyse":"@trim(item()?['analyse '])","$DoMenu2":"mfInsert"}},"runAfter":{"Select_solution1":["Succeeded"]},"metadata":{"operationMetadataId":"79170215-a528-4286-9be5-06e4ee0b7ae6"}}},"runAfter":{},"metadata":{"operationMetadataId":"9a79127d-9feb-482e-a4ca-4dbcfe64011a"}}}

[autoperfect93]

Hi @Matthy79 @grantjenkins ,

I now have a JSON array of objects as shown below:

How can I update values inside the "Cparty" item? I need to update the names of the counterparties based on a separate excel table saved in SharePoint folder. Here is the table:

For example I have "Cparty": "SI03-MAIN 3521570153" which I need to update to "Cparty": "SI03-MAIN" by fetching the data from column B "New Counterparty name" in the excel table.

[Matthy79]

Hello @autoperfect93 

I would suggest to create a lookup object from your excel table and use select to set the property information by using SetProperty. I am currently busy and not on the computer. If this information is not enough just create a new topic and I am sure somebody can help with this issue.