How to merge two arrays in Power Automate by a common property using the xpath expression.

In this video I demonstrate how you can quickly merge two arrays in Power Automate that have a common property. This is a response to a question on the Power Automate Community Forums:

This is quite a common requirement that normally involves the use of inefficient loops. The method described in this video uses a Select action combined with the powerful xpath expression.

[PaulieM]

Check out the fastest and easiest method for merging arrays in Power Automate:

Power Tools for Power Automate - Merge Arrays

[vsone]

Thank you for this solution! I'm sure your video has saved many hours worth of unnecessary looping!

[danielowenuk]

Been trying to figure out how to do this for three days! I had watched a few videos, posted on the PA forum and Reddit. Eventually decided to try another youtube video to see if it it was any clearer and yours did the trick!

[vinamrachandra]

Thanks Paulie,
This is simply genius. Total is more than the sum of parts.

[darrindecosta]

Paul - a great solution! I ran into some arrays of zero length, so needed to add some error handling to it to make it work. Here's my solution:
if(
empty(
xpath(
xml(outputs('XML')),
concat(
'//array[ID/text()="',
item()['Id'],
'"]/StartDate/text()'
)
)
),
addProperty(
item(),
'StartDate',
'2000-01-01T12:00:00Z'
),
addProperty(
item(),
'StartDate',
xpath(
xml(outputs('XML')),
concat(
'//array[ID/text()="',
item()['Id'],
'"]/StartDate/text()'
)
)[0]
)
)

[GillesUrena]

Great post. I was looking for a solution for a couple of days. Thanks!

[AmandaMurawski]

This was such a huge help! Thank you so much for putting together. And the ?[0] addition in the comments was a lifesaver. Thank you!!

[LM-nqph]

This is gold.. glad that i have found this video.. with the loops automate was taking 20 min.. with this method its only taking 37 seconds 😊.. thank you

[devothunder]

wow, such a clever way to merge two arrays based on a common property. thank you so much for your content.

[DanielRodriguez-pd]

Thanks for this guide, it really helped me plow through hundreds of records I needed to consolidate between two SPO lists! Only two hiccups I ran into were handling occasions were:

-When the xpath array was empty, all I had to use was ?[0], and it took my far too long to remember how to do that!
-Xpather kept throwing me off because "beautifying" my xml code would cause the closing part of my elements to end up on a different line which would make the query expressions fail. Once I realized that, I simply left the xml code as one line and the testing succeeded.

[samuelpierre]

You're a genius! I would have never thought of that.

[csmith]

Thanks for the great video; very innovative approach!! In fact, amazing gymnastics with that XML functionality; wish there was a more straightforward way to do this, but props to you for a go-to solution!

[tyumantop]

Man I appreciate your time and the knowlegde shared with us. Thank you!!!

[พิชญะชัยชนะ-คต]

Thanks ! You help me a lot to avoiding boring apply to each!

[foursevenzeroninenineone]

You're beyond amazing for this one. Holy crap, you legend

[jamesclark]

Thank you again for helping me understand this concept. I actually need this.

[jsardan]

I'm late, but your video was very helpful. Thanks

[gregoryk]

Great video, greater presentation, helped me a lot. Thanks!

[PavanCh-qu]

Hi Paul, great content...

I have similar use, but I don't have "ID" field in my source data...Below are the details in terms of source data and

The source data (I am getting in Power BI report and same needs to be exported to csv/excel in the output as I mentioned previous comment and the source data:

Category SubCategory
Office Supplies Appliances ABC XYZ XABS AG-1 Task1 10 5
Office Supplies Appliances ABC XYZ XABS AG-1 Task2 15 10
Office Supplies Appliances ABC XYZ XABS AG-1 Task3 25 10
Office Supplies Appliances ABC XYZ XABS AG-1 Task4 15 5
Office Supplies Appliances ABC XYZ XABS AG-1 Task5 15 10

In total there will be 8 dimensions/categorical fields as of now I have mentioned only 7 from Category to Task (The user has the fleixibility to select the list of fields from Category to Task) at least they will select till Column5 for sure so need to show aggregated value that I was able to achieve and there will be 8-10 measures and currently I have mentioned (Sales/Profit)...The main child values will need to show as a child/append rows from Column5....Till Column5 I was able to achieve using json structure with xpath, but struggling to get from Type and Task...


The expected output is:
Name Sales Profit
Column1 500 200
Column2 500 200
Column3 500 200
Column4 500 200
Column5 500 200
Type1 200 50
Task1 100 50
Task2 100 50
Type2 300 100
Task1 100 50
Task2 100 50




Any suggestions or solution...as I am working on real scenario, but got stuck...need help...thanks in advance..

[poongodhaim]

Hi paulie, i am facing issues while trying to add the combo box choice value to my first array.text(), string() what should we use for combo choices?