I have two arrays
[
{
"CustomerName": "Customer A",
"CustomerId": "1",
"CustomerPersonId": "1",
"CustomerPersonName": ""
},
{
"CustomerName": "Customer B",
"CustomerId": "2",
"CustomerPersonId": "1",
"CustomerPersonName": ""
},
{
"CustomerName": "Customer C",
"CustomerId": "3",
"CustomerPersonId": "2",
"CustomerPersonName": ""
}
]
And
[
{
"PersonName": "PersonName 1",
"PersonId": "1"
},
{
"PersonName": "PersonName 2",
"PersonId": "2"
}
]
I want to create an array in power automate that looks like this
[
{
"CustomerName": "Customer A",
"CustomerId": "1",
"CustomerPersonId": "1",
"CustomerPersonName": "PersonName 1"
},
{
"CustomerName": "Customer B",
"CustomerId": "2",
"CustomerPersonId": "1",
"CustomerPersonName": "PersonName 1"
},
{
"CustomerName": "Customer C",
"CustomerId": "3",
"CustomerPersonId": "2",
"CustomerPersonName": "PersonName 2"
}
]
So the thing that i want is to do a lookup on customerpersonId and get the name of that person and add that to the first array.
I would prefer not to be forced to use variables since i have many rows to update and want to make it quick. I tend to belive that using vriables does not allow a for each loop in power autoamte to be that fast.
Hi @Oskarkuus
The idea is to convert the JSON into XML & using xpath query we can filter required value from xml node. The same concept applies to html also.
You can check out my blog where I have explained with a use case:
Power Platform Join arrays efficiently without using loops in Power Automate Select (technetty.com)
If this helps & solves your problem, please remember to give a 👍 and accept my solution as it will help others in the future.
Thanks
Hi,
sharing another solution:
Flow:
Filter array
Append to array variable
Output:
Regards
Kaif
This method is amazing. I am a bit annoyed over that i can not figure out what it does tho 😛
Do you mind explaining this:
xpath(xml(json(concat('{"Root":{"Item":',outputs('Compose_2'),'}}'))),concat('string(//Root//Item/PersonId[text()=','''',item()?['CustomerPersonId'],'''',']/../PersonName/text())'))
Hi @Oskarkuus
Here is the sample flow. I have stored both the arrays in compose actions:
Next, add Select action and pass the output of compose action in "From" parameter. For each value of key in Map parameter, expression needs to be added:
Expression needs to be entered in the expression box as highlighted in the above screenshot.
The output of select action will give the desired array:
If this helps & solves your problem, please remember to give a 👍 and accept my solution as it will help others in the future.
Thanks
WarrenBelz
146,745
Most Valuable Professional
RandyHayes
76,287
Super User 2024 Season 1
Pstork1
66,091
Most Valuable Professional