This post describes how to merge two separate arrays that contain a common value. My friend Tom Riha recently wrote a good post on how to do this. His method will work well for a small dataset, but for a large array it would be quite time consuming as it uses an Apply to each loop.
I also did a video on this subject in response to the a question on the Power Automate Forums:
The Example Data
We are going to combine two arrays, here is the first one:
[
{
"Name": "Tom",
"Country": "CZ"
},
{
"Name": "Paul",
"Country": "UK"
}
]
The second array:
[
{
"Name": "Tom",
"Age": "younger than Paul"
},
{
"Name": "Paul",
"Age": "older than Tom"
}
]
The combined array we want to create:
[
{
"Name": "Tom",
"Country": "CZ",
"Age": "younger than Paul"
},
{
"Name": "Paul",
"Country": "UK",
"Age": "older than Tom"
}
]
In order to combine these into a single array, we need a common value in each array. In this example the Name key is common to both arrays. We are going to use the powerful xpath expression to perform this in a single step.
An easier way!
As you can see in this article, merging arrays in Power Automate is a lot of work. But it doesn’t have to be!
You can follow the guide below to merge arrays using multiple actions and expressions, or you can do it with a single action I developed! The choice is yours!
Array Preparation
Before we can use xpath, the second array needs to be converted to XML, and before we can do that, we need to do some preparation. Check out this screenshot:
All of the above actions are Compose actions. The step named PrpareArray2 simply wraps the output of Array2 around some additional JSON and produces the following output:
{
"Root": {
"Array": [
{
"Name": "Tom",
"Age": "younger than Paul"
},
{
"Name": "Paul",
"Age": "older than Tom"
}
]
}
}
Now that it is in the proper format the step named XML converts the array to XML and produces the following output:
<Root> <Array> <Name>Tom</Name> <Age>younger than Paul</Age> </Array> <Array> <Name>Paul</Name> <Age>older than Tom</Age> </Array> </Root>
Combine the Arrays
With all of that prep work done, we can begin to actually merge the arrays. See this Select action:
As you can see, all of the work is done in a single select action, but it requires a bit of explanation. The first part is straightforward:
- The input for the select is Array1
- The Keys Name and Country are drawn from Array1 using the item() syntax.
- The Age key is pulled from the XML using the xpath expression.
The xpath expression is easier to read like this:
xpath
(
outputs('XML'),
concat('//Array[Name/text()="', item()['Name'], '"]/Age/text()')
)?[0]
The concat function is used to dynamically create an xpath query by using the name from the current item. So in the first iteration of the loop the xpath expression would be:
//Array[Name/text()="Tom"]/Age/text()
What are we asking xpath to do here:
- Find all of the elements within Array
- Only find nodes within Array where Name is equal to Tom
- Grab the text value of the Age where the Name matched.
You can test this out for yourself by copying the XML above and pasting it into xpather.com – then copy the expression above and you should see results like this:.
xpath will return an array of results, even if there is only a single match, so the final part of the expression is: [0] to grab the first result of the array.
So in a single select action we can combine the arrays easily. In our example we could simply it further by using the addProperty function in the select instead of key/value pairs.
In this example the Select action was changed to text mode instead key/value mode. Then I used the expression:
addProperty
(
item(),
'Age',
xpath(outputs('XML'), concat('//Array[Name/text()="', item()['Name'], '"]/Age/text()'))?[0]
)
This simply takes the current item and adds a new property called Age and assigns the value returned from the xpath expression.
Conclusion
Which method should you use and is this method worth the hassle?
It depends on how performance dependant your flow is. If you are only working with a small amount of data, the method Tom shows is easier and will execute quickly.
If you are working with hundreds or thousands of rows of data, then this method will be much faster. It’s always fun to compare different methods of doing the same thing!
Support This Website
Thank you for visiting. It’s because of readers like you that I continue to write on this blog.
If you found value in what I share, I’ve set up a ‘Buy Me a Coffee’ page as a way to show your support.
Evolvous says
Great Share! Thank you for Sharing.
Max says
You definitely need a „like“-button on your site.
Thanks!
Cyril says
Nice post.
Another xpath tester / generator: https://extendsclass.com/xpath-tester.html
Somni says
Hello,
Thank you very much for all you do. It helps a lot. But this time, I don’t understand why I’ve got an error: ” ‘The template language expression ‘xpath(outputs(‘XML’),concat(‘//Array[ID/text()=”‘,item()[‘ID’],'”]/Seuil/text()’))[0]’ cannot be evaluated because array index ‘0’ cannot be selected from empty array. ” (Instead of “name” like in your example, I’m using an ID so I followed your video).
The thing is my array is from a spreadsheet (Excel online) that I put in a variable array….Is that the problem? If so, what can I do? I need to join 2 spreadsheed but I had some “distinct” and “sum” to do with the first one before joining the 2 sheets together.
Somni says
OK, so I tested different scenarios and it seems like when I have more than 3 items in the array1, it doesn’t work. When I juste have 3 items, it works perfectly. How can I do to merge when I have 106 lines in the array1 ?
Somni says
Me again, sorry if I “spam” you. You can delete all my comments. I found out where the problem was: I had more ID on array1 than array 2. The error message was not very explicit…..
David Haygood says
Hi Somni,
I experienced the same issue, but when I added a ‘?’ before the index it worked. So try this…Seuil/text()’))?[0]
Hi Paulie, thank you so much for your great time saving logic. It is truly appreciated.
Katrina says
Love your solutions, would it be possible to post an example using sharepoint lists/update item?
Pablo says
@David Haygood thanks for your comment, got my flow working! What does the ? do in that case?
Jason says
Thank you for documenting and sharing this approach, it’s exactly what i wanted to achieve.
Emmanuel says
Great post Paul! Would it also work if you used First function like this?
First(xpath(outputs(‘XML’),concat(‘//Array[ID/text()=”‘,item()[‘ID’],'”]/Size/text()’)))
Elizabeth says
This is great! Excellent video and article. A true gem.
robert says
This is createing a left outer join, how can we change this to create an inner join, so only select the item that matches one in the array 2?
Ricardo Bunge says
Paulie, this is fantastic — have used it several times with great success. Have a question about a slight variation.
Have two arrays, but they don’t contain objects, just concatenated text. For instance,
[
“john, apple, 6”,
“mary, pear, 10”
]
and
[
“john, blue”.
“mary, purple”
]
the john and mary bits will always be unique. I’m looking to merge the two matching on the name, so the resulting array will be as follows:
[
“john, apple, 6, blue”,
“mary, pear, 10, purple”
]
is this possible?
Paulie says
Totally possible.
François says
Thank you sooooo much, Paul.
This is a great post, very well explained, down to the smallest details.
Icing on the cake, it really does a left outer join, which is exactly what i wanted.
Paulie says
So pleased it helped François!
SynapseArtist says
Very helpful!
would like to know how to approach for merging 2 arrays on more than 1 field.
thanks!
Alex Lindberg says
Thank you for your blog and responding to questions. I have duplicated your flow but the xpath statement returns an error:
The Xpath: “xpath(outputs(‘XML’), concat(‘string(//Array[Name/text()=”‘, item()[‘Name’], ‘”]/Age/text())’))”
returns the following error:
Unable to process template language expressions in action ‘TestXPATH’ inputs at line ‘0’ and column ‘0’: ‘The template language expression ‘xpath(outputs(‘XML’), concat(‘//Array[Name/text()=”‘, item()[‘Name’], ‘”]/Age/text()’))’ cannot be evaluated because property ‘Name’ cannot be selected. Please see https://aka.ms/logicexpressions for usage details.’.
I have tried using the string() function and add adding a ‘?’ to item()?[‘Name’] to no avail.
Thank you for any guidance you can offer.
FRANCOIS LIGOUY says
Strictly duplicating Paul’s flow works perfectly well; i would say you definitely don’t need the “string” function, although using it still yields expected result, provided you don’t use the final “?[0]” that Paul wrightly added in order to make sure we only grab the first result of the array.
The only reason I can think of is that you misspelled the name of the (Name) property in the “item()[‘Name’]” part of the “xpath(outputs(‘XML’)…” expression.
Doing it on purpose triggers a similar error to the one you report.
Of course, you also want to make sure that simple quotes are not copy-pasted but rather typed using your own keyboard.
robert says
how can we append an array and not a text(), using the xpath ?
Alex says
For those in need of a 1 step no code solution, the Encodian connector has a utility action called Combine Arrays that does exactly this:
Utility actions are cheaper (consume 0.05 credits per operation on paid plans) but it does require an Encodian subscription.
https://support.encodian.com/hc/en-gb/articles/15423363008412-Utility-Array-Combine