How to merge two arrays in Power Automate by a common property using the xpath expression.
Vložit
- čas přidán 7. 08. 2024
- 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:
powerusers.microsoft.com/t5/B...
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. - Jak na to + styl
Thank you for this solution! I'm sure your video has saved many hours worth of unnecessary looping!
Thanks Paulie,
This is simply genius. Total is more than the sum of parts.
wow, such a clever way to merge two arrays based on a common property. thank you so much for your content.
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!
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]
)
)
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
Glad you found it useful, it’s an unexpectedly popular video.
You're a genius! I would have never thought of that.
Great post. I was looking for a solution for a couple of days. Thanks!
Glad it worked out well for you. Certainly a lot faster than other methods available in Power Automate.
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.
Glad it helped! Sorry I didn’t include the information on the question mark syntax.
Thanks ! You help me a lot to avoiding boring apply to each!
Great video, greater presentation, helped me a lot. Thanks!
Thank you again for helping me understand this concept. I actually need this.
It’s dead handy and sometimes the only way to do this in an efficient manner. You’re very welcome!
Super helpful, thanks for posting
I'm late, but your video was very helpful. Thanks
You're beyond amazing for this one. Holy crap, you legend
😂 thank you!
@paulie, exactly what i was looking for. Brilliant work, thank you for your thorough explanation. Great stuff.
Pleased to hear it was helpful.
What a great trick!!! Thank you so much for sharing. My thumbs up and sub for you!
Great video! If I wanted to add multiple properties to the object, would I use a concat at the start of my expression or within the existing concat you have?
One more question.. in some cases, the item dont exist in xml, so, it returns error (cannot be evaluated because array index '0' ).
So, i made a condition using if and empty, to check, and after return the real value, or return another text that i set.
It worked also, BUT, you are the guy that always has another smarter idea =)
Hey there THANK YOU!!! Could you tell me how to do this with multiple common values?
This is Great! Thanks Paulie.
Is there a way to do full outer join?
I mean, I need to get the record with ID 4444 as well.
Hi Paulie, this is an amazing video, thanks for sharing.
If i need to add another property, of the same xml, i add another AddProperties, repeating all the same sintaxy.
It worked, but, do you have any other smarter idea?
Hi,
Thanks for the solution. Really, an easy way.
I am getting an issue with large set of data. I have converted 16k of arrays data into XML format and tried to compare an array size of 19k objects and it is taking more than 30 mins for it.
Is it because of array length or am I don't anything wrong.
Paulie, can you explain why you had to use: " ' , item()['ID'] , ' "
- The single quotation marks?
- The comma at the beginning and at the end of the expression?
Thank you for your help!!
item()['ID'] is to output the ID of the current item within the xpath expression. So for each entry of the array, this will be whatever the ID property is for that record. The commas around that part are because that value is wrapped inside a concat expression which is joining together the parts of the xpath expression.
I’m stuck because my append to array variable is adding multiple arrays so I would have to manually separate them and then rejoin them back as one.
Hello - if the value I'm trying to return is a number rather than a string - how can we do this? The result using your existing formula is being interpreted a string not integer value. Thanks.
It would work no matter the values in Array2 are numbers or text. Data will be stored as string in the new array.
If further within your flow you need to use those values as numbers, just add the int function.
For example, you can do an apply to each loop from the output of the select action. Then, if you need to compare the value as a number, just use int(items('Apply_to_each')?['Size'] when getting the values.
Hi Paulie... so in your example you are grabbing a variable from another array to append to the original array. In my case, I need to grab values from 4 different arrays to combine and it's really slow doing this in "loops" now (takes 6 mins to run even though they are small arrays (under 50) .. it sure would make the final loop thru' the finished array so much easier when producing the final output. My point is in theory the select could reach across multiple arrays to merge not just one....yes?
Yes, you cups reach across as many arrays as you wanted to
@@PaulieM I have a dumb question as a I try and figure this all out - I have 2 arrays (array1=100 items containing loads of variables + array2=5 items, containing an ID value) - I want to merge these 2 and get back the array2 items with all the variables from array1... so therefore my select should be from "array2" and adding multiple properties from array1 right? therefore still end up with 5 items in the array but with loads of vars...
Array 1 looks like this...
9
9
Creation, collection, Standardization, Review and Storage of Global Policies....
Array 2 looks like this..(currently only 1 item)
[
{
"ID": 1
}
]
My select is this...
From: @{body('Parse_JSON')} - which refers to Array 2
Map: addProperty(item(),'ReportId',xpath(outputs('XMLReportIds'),concat('//Array[ID/text()="', item()['ID'],'"]/ID/text()'))?[0]) - where array1 = outputs('XMLReportIds')
I added the ?[0] because I was getting an error. Now I get 1 item in the array with a new var added called ReportId: null...
There is a value for ID=1 in the body('Parse_JSON') so why isn't it finding it?
I tried xpather.com to try and debug it but it was complaining about the massive paste of the body('Parse_JSON') but I did wonder if item()['ID']="1" or should it be item()['ID']=1
You should add a ? before the [0] -- this way if it doesn't find a matching value the entire thing doesn't fail and it just skips that record.
Good point. I might actually do a short video on the meaning on the ? symbol. Everyone puts it in, but I am not sure everyone knows why they are putting it on and what it does.
love this method, can you suggest me something to merge three arrays. ????
The same method would work for three!
@@PaulieM I'm new to pa. Can u please elaborate this a bit more !?
If you have two arrays with both having two columns that you want to join on, can you do this same task? How would you change the xml coding to do the multiple joins?
It’s a good question - I will setup something similar and check it out.
@@PaulieM to add to this question, does it matter for the xml value that you are pulling into your first array is an integer and the second array will not have all the id’s that are in the first array causing a null value in the merge?
@@PaulieM did you find it? if yes could you please share it
Hello!! thank you so much for this video!! I have been busting my brain over something like this!!... I do have a question... is it possible to add to conditions??? I have an SP List and a signatures collection pulled from powerapps... in this collection, I have 3 signatures to append to my main SP List, and this depend on 3 values that need to match (Code, Facility and Role)... is it possible to do so?? I posted a question on the Power Automate Forum (with a different approach), but no help yet... so I have been trying to think outside the box and tried merging 2 arrays
I’m sure you could do something like you’re trying to do. Did you get a response on the forum?
@@PaulieM I did get a reply. I was suggested to use Union(), but didn't really work as I expected, so I worked around my problem... I created a big collection inside powerapps, and added ALL the info I needed (the 3 columns I mention and the info from my SP list)... and before running the flow, I apply a filter. It is a bit long, but works perfectly.
Hi, I probe it and work perfect but need a little more....If there is no an ID in Array1 (yes in Array2) take a error, how can avoid....Thank you so much..
Response: I Check the answer above, thanks
hi Paul, if array 2 hasnt unique ID's but does have unique Sizes, how to handle this? i replicated your flow but couldnt figure this out to be honest.
Check out the range expression - it will do what you need. If you get stuck i will make you a short video
i have this expression now@@PaulieM
addProperty(item(), 'FileName', xpath(outputs('XMLarray2'), concat('/Root/Array[Task_x0020_tag_x0020_1/text()="', item()?['TaxTask[TaxTaskId]'], '"]/Name/text()'))[0])
however, i have non-unique Task_x0020_tag_x0020_1 but with unique 'FileName' now i get only one appended result while it could be maybe 2-3 or more
@@PaulieMcan you please advise
@@mk15minut64 use the range expression combined with length as the source for a select action and then you will be able to merge the arrays based on their index
thank you sir, i emailed you how i resolved this one!
12k item never finished processing at 19 hours still running.
Amazing video very helpful! One of my arrays will have some blanks in some fields. Is there a way to handle that? I've tried a few ways but keep getting this
The execution of template action 'Case_select' failed: The evaluation of 'query' action 'where' expression '{
"Case": "@item()?['number']",
"Account": "@xpath(outputs('XML_accounts'), concat('//Array[sys_id/text()=\"', item()['Account'], '\"]/name/text()'))[0]",
"Description": "@item()?['short_description']",
"Assignee": "@xpath(outputs('XML_assignees'), concat('//Array[sys_id/text()=\"', item()['assigned_to'], '\"]/email/text()'))[0]",
"CaseSysId": "@item()?['sys_id']"
}' failed: 'The template language expression 'xpath(outputs('XML_assignees'), concat('//Array[sys_id/text()="', item()['assigned_to'], '"]/email/text()'))[0]' cannot be evaluated because array index '0' cannot be selected from empty array. Please see aka.ms/logicexpressions for usage details.'.
You could try: xpath(outputs('XML_assignees'), concat('//Array[sys_id/text()="', item()['assigned_to'], '"]/email/text()'))?[0] instead which will return null instead of causing an error.
@@PaulieM Worked like a charm, thanks!
@@PaulieM Lovely, helped me so much!!
@Paulie M, thank you for the great solution. For some reason, when I do the last action I get an error:
The execution of template action 'Select' failed: The evaluation of 'query' action 'where' expression '@addProperty(item(), 'ST', xpath(outputs('XML'), concat('//Array[ID/text()="', item()['ID'],'"]/ST/text()'))[0])
' failed: 'The template language expression 'addProperty(item(), 'ST', xpath(outputs('XML'), concat('//Array[ID/text()="', item()['ID'],'"]/ST/text()'))[0])
' cannot be evaluated because array index '0' cannot be selected from empty array. Please see aka.ms/logicexpressions for usage details.'.
It says that my array is empty, but my both arrays are not empty. Do you know by chance what is wrong?
I am experiencing the same error when I attempt to run the flow. I hope we can get a response!
Check the answer above. This error is shown when an item in Array1 is not found in Array2. Just add a ? before [0] at the end of the formula:
addProperty(item(), 'ST', xpath(outputs('XML'), concat('//Array[ID/text()="', item()['ID'],'"]/ST/text()'))?[0])
This will return null for those values in Array1 that are not present in Array2
You're a genius! I would have never thought of that.
Thank you! Let me know if you’re stuck with anything else.