Updating Array Property Values by Looking Up Another Array in Power Automate Cloud flow

[Nived_Nambiar]

Hi everyone,

In this article i will be sharing easy approach of updating an array by looking up another array values. Let's see how 🙂

Flow Design Logic Overview

Let me first give overview of the logic which i will be using here to update the arrays. So for example, let's take two arrays array1 & array2. We need to update array1 using data from array2. So here array2 will be converted to XML format and then use xpath to compute whether the value is present for iteration of array1 in array2 (which is in xml format) and then updating array1 accordingly.

To understand the logic properly, An example is provided below:

Example 

Here an Instant Cloud flow will be created for demonstration purposes

Also we will be using two arrays Array1 and Array2  as shown below.

Array1

[
  {
    "EmpID": 1289,
    "EmpName": "John",
    "Location": ""
  },
  {
    "EmpID": 1360,
    "EmpName": "Jack",
    "Location": ""
  },
  {
    "EmpID": 1571,
    "EmpName": "James",
    "Location": ""
  }
]

Array2

[
  {
    "EmpID": "1289",
    "Location": "New York",
    "DOJ": "20/09/1997"
  },
  {
    "EmpID": 1361,
    "Location": "Canada",
    "DOJ": "12/03/2020"
  },
  {
    "EmpID": 1572,
    "Location": "London",
    "DOJ": "08/01/2000"
  }
]

Goal here is to update Array1 Location property value by lookup of Location property values from Array2 using EmpID as primary field.

So let's see how to get it step by step !

Step 1: Use Initialize Variable actions to initialize two Array type variables - Array1 and Array2 as shown below:

Step 2: Use Compose action to create a JSON structure as below for Array2 variable. [ This would be used later for XML format conversion]

{
  "Root": {
    "data": @{variables('Array2')}
  }
}

Step 3: Use Select action to update the Location property of each element of the Array1 by lookup of Array2.

Since we are not updating EmpID and EmpName property of Array1, so we can keep the same value of them as in Array1 like below:

Let's see how the Location property has been updated, which is the most important point to be discussed.

The below expression has been used to update the Location Property.

So let me denote the expression used for location update by [Expression1] for easy reference.

if(equals(length(xpath(xml(outputs('Compose')),concat('//data[EmpID=',item()?['EmpID'],']/Location/text()'))),0),'',xpath(xml(outputs('Compose')),concat('//data[EmpID=',item()?['EmpID'],']/Location/text()'))?[0])

Looking at this expression may seems to be complex at first sight, But let me make it easy by step by step :).

So overall the [Expression1] is an If function which checks for some condition, if it is true or false assign some values which would be output.

So let's go through an important expression , which would be necessary to understand [Expression1].

Let's denote below expression as [Expression2] 

xpath(xml(outputs('Compose')),concat('//data[EmpID=',item()?['EmpID'],']/Location/text()'))

[Expression2] is a xpath function which helps to compute the Location values from Array2 based on EmpID attribute and returns the result in array of Location values.

This expression uses Array2 in XML format xml(outputs('Compose')) by converting the json structure as in the Compose Action (Step 2). It uses XPath concat('//data[EmpID=',item()?['EmpID'],']/Location/text()')  which helps to compute Location node values from Array2 (in XML format) when its EmpID node text value equals the EmpID property value of Array1.

As said above, [Expression2] returns Location values in array format. So based on this we can go to [Expression1] to understand it better.

As said earlier , [Expression1] is an If function. It checks whether the output of [Expression2] has elements in array or not, if there are no elements in array, it will assign Location property with value ''. Else it will assign xpath(xml(outputs('Compose')),concat('//data[EmpID=',item()?['EmpID'],']/Location/text()'))?[0] which is first element of output array of [Expression2].

So overall flow looks like below:

So let's run the flow and see its output of select action:

As you can see for the element with EmpID property 1289, Location value is updated with New York and since rest of employees data were not present in Array2 it was not updated. Hence the flow worked as expected.

This is basic example on how you can utilize XML while comparing/updating two arrays. You can extend same thing according to your usecases 🙂

Hope this give you a clear idea on updating 2 arrays.

--------------------------------------------------------------------------------------------------------------------------------------

Let me know your thoughts/doubts after going through this blog.

Thanks & Regards,

Nived N

[Prof_E]

Had to login just to give this article the THUMBS UP it deserves.

Very beautiful and effective article. Very helpful.

Thanks @Nived_Nambiar 

[eukali]

Thanks for this post Nived_Nambiar!

The solution looks pretty good & I try to establish something similar. However, when trying to apply the solution to my data, I get a response from PowerAutomate that Xpath code would not be written correctly. As I spent some time on it & could not get it to work, I would appreciate your help or the help of others.

As in your example, I have 2 arrays:

Array "Get items Campaign list", which should be updated (BasicStartDate/DueDate) and is of following content (I deleted to a minimum as example): 

"value": [           {

                "ID": 1583,

                "PO": "000001066763",

                "BasicStartDate": "2023-02-20",

                "DueDate": "2023-04-17",

                "Order0": "1066763" (-> this is a calculated field in SharePoint list)

               }   ]

I played around with the output of "Get items" applying a select action to filter the output and creating a variable containing the content as well. Basically, this was not expected to make any difference and the outcome of the flow was the same.

Array "Compose", which is based on data from an Excel file (I deleted to a minimum as example):

{ "Root": { "data": [ { "PO": "1066763", "Start": "2024-02-13", "End": "2024-04-22" } ] } }

This is my flow:

Select action has following code for Start & End:

Start: if(equals(length(xpath(xml(outputs('Compose')),concat('//data[PO=',item()?['Order0'],']/Start/text()'))),0),'', xpath(xml(outputs('Compose')),concat('//data[PO=',item()?['Order0'],']/Start/text()'))?[0])

The key was set to Start and BasicStartDate depending on the input array.

End: 

if(equals(length(xpath(xml(outputs('Compose')),concat('//data[PO=',item()?['Order0'],']/End/text()'))),0),'', xpath(xml(outputs('Compose')),concat('//data[PO=',item()?['Order0'],']/End/text()'))?[0])

The key was set to End and DueDate depending on the input array.

This message is shown when I run the flow:

The execution of template action 'Select_Campaign_list' failed: The evaluation of 'query' action.....

"The template language function 'xpath' parameters are invalid: the 'xpath' parameter must be a supported, well formed XPath expression. Please see https://aka.ms/logicexpressions#xpath for usage details.'."

Your help is very much appreciated!

Many thanks and kind regards