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

Рет қаралды 16,520

Paul Murana

Күн бұрын

Пікірлер: 67

@vsone9565 Жыл бұрын

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

@darrindecosta4995 2 ай бұрын

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] ) )

@vinamrachandra9611 Жыл бұрын

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

@c016smith52 2 жыл бұрын

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!

@devothunder5210 Жыл бұрын

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

@user-yp5vc9bv1q Ай бұрын

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.

@PaulieM Ай бұрын

Glad it helped! Sorry I didn’t include the information on the question mark syntax.

@samuelpierre7762 Жыл бұрын

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

@user-vt4yx6vt5t 5 ай бұрын

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

@PaulieM 5 ай бұрын

Glad it worked out well for you. Certainly a lot faster than other methods available in Power Automate.

@LM-nq8ph Жыл бұрын

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

@PaulieM Жыл бұрын

Glad you found it useful, it’s an unexpectedly popular video.

@user-dj5ss3ls8p 2 ай бұрын

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

@gregoryk6702 Жыл бұрын

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

@paulmatkin1762 Жыл бұрын

Super helpful, thanks for posting

@jamesclark2020 5 ай бұрын

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

@PaulieM 5 ай бұрын

It’s dead handy and sometimes the only way to do this in an efficient manner. You’re very welcome!

@foursevenzeroninenineone2479 2 жыл бұрын

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

@PaulieM 2 жыл бұрын

😂 thank you!

@ImuRazz Жыл бұрын

@paulie, exactly what i was looking for. Brilliant work, thank you for your thorough explanation. Great stuff.

@PaulieM Жыл бұрын

Pleased to hear it was helpful.

@jsardan1387 Жыл бұрын

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

@kimsalas8197 2 жыл бұрын

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?

@TwentyNailsBike Жыл бұрын

What a great trick!!! Thank you so much for sharing. My thumbs up and sub for you!

@petrinnn Жыл бұрын

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 =)

@johanneszachariou3208 Жыл бұрын

Hey there THANK YOU!!! Could you tell me how to do this with multiple common values?

@JohnVittney Жыл бұрын

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.

@petrinnn Жыл бұрын

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?

@noodlebrains 2 ай бұрын

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.

@user-ed2qp2ur1g 11 ай бұрын

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.

@ramsayzaki Жыл бұрын

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.

@PaulieM Жыл бұрын

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.

@matheusosa 2 жыл бұрын

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!!

@PaulieM 2 жыл бұрын

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.

@brendanbowen7097 2 жыл бұрын

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.

@TwentyNailsBike Жыл бұрын

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.

@richardclowes6166 Жыл бұрын

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?

@PaulieM Жыл бұрын

Yes, you cups reach across as many arrays as you wanted to

@richardclowes6166 Жыл бұрын

@@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

@Launcelot705 Жыл бұрын

12k item never finished processing at 19 hours still running.

@IgnacioPerezSantaella 11 ай бұрын

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

@Fernanda-uh7iz Жыл бұрын

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

@PaulieM Жыл бұрын

I’m sure you could do something like you’re trying to do. Did you get a response on the forum?

@Fernanda-uh7iz Жыл бұрын

@@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.

@shellybarrett2960 Жыл бұрын

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?

@PaulieM Жыл бұрын

It’s a good question - I will setup something similar and check it out.

@shellybarrett2960 Жыл бұрын

@@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?

@nikhilmudgal Жыл бұрын

@@PaulieM did you find it? if yes could you please share it

@arjundoespowerapps Жыл бұрын

love this method, can you suggest me something to merge three arrays. ????

@PaulieM Жыл бұрын

The same method would work for three!

@arjundoespowerapps Жыл бұрын

@@PaulieM I'm new to pa. Can u please elaborate this a bit more !?

@mk15minut64 6 ай бұрын

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.

@PaulieM 6 ай бұрын

Check out the range expression - it will do what you need. If you get stuck i will make you a short video

@mk15minut64 6 ай бұрын

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

@mk15minut64 6 ай бұрын

@@PaulieMcan you please advise

@PaulieM 6 ай бұрын

@@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

@mk15minut64 6 ай бұрын

thank you sir, i emailed you how i resolved this one!

@daveedd4406 2 жыл бұрын

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.'.

@PaulieM 2 жыл бұрын

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.

@daveedd4406 2 жыл бұрын

@@PaulieM Worked like a charm, thanks!

@TwentyNailsBike Жыл бұрын

@@PaulieM Lovely, helped me so much!!

@onlinebusinessru Жыл бұрын

@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?

@debbysheaanderson333 Жыл бұрын

I am experiencing the same error when I attempt to run the flow. I hope we can get a response!

@TwentyNailsBike Жыл бұрын

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