Check out the fastest and easiest method for merging arrays in Power Automate: Power Tools for Power Automate - Merge Arrays ru-vid.com/video/%D0%B2%D0%B8%D0%B4%D0%B5%D0%BE-yEjlDMj-4L8.html
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] ) )
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.
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
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!
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 =)
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?
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.
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.
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 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
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, 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
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, 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.
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 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?
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 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.
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 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
@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?
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
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.
