• Skip to primary navigation
  • Skip to main content
  • Skip to primary sidebar

Tachytelic.net

  • Get in Touch
  • About Me

How to merge arrays in Power Automate

July 12, 2022 by Paulie 2 Comments

This post describes how to merge two separate arrays that contain a common value. My friend Tom Riha recently wrote a good post on how to do this. His method will work well for a small dataset, but for a large array it would be quite time consuming as it uses an Apply to each loop.

I also did a video on this subject in response to the a question on the Power Automate Forums:

The Example Data

We are going to combine two arrays, here is the first one:

[
    {
        "Name": "Tom",
        "Country": "CZ"
    },
    {
        "Name": "Paul",
        "Country": "UK"
    }
]

The second array:

[
    {
        "Name": "Tom",
        "Age": "younger than Paul"
    },
    {
        "Name": "Paul",
        "Age": "older than Tom"
    }
]

The combined array we want to create:

[
  {
    "Name": "Tom",
    "Country": "CZ",
    "Age": "younger than Paul"
  },
  {
    "Name": "Paul",
    "Country": "UK",
    "Age": "older than Tom"
  }
]

In order to combine these into a single array, we need a common value in each array. In this example the Name key is common to both arrays. We are going to use the powerful xpath expression to perform this in a single step.

Array Preparation

Before we can use xpath, the second array needs to be converted to XML, and before we can do that, we need to do some preparation. Check out this screenshot:

Power Automate Flow preparing an array to be merged

All of the above actions are Compose actions. The step named PrpareArray2 simply wraps the output of Array2 around some additional JSON and produces the following output:

{
  "Root": {
    "Array": [
      {
        "Name": "Tom",
        "Age": "younger than Paul"
      },
      {
        "Name": "Paul",
        "Age": "older than Tom"
      }
    ]
  }
}

Now that it is in the proper format the step named XML converts the array to XML and produces the following output:

<Root>
	<Array>
		<Name>Tom</Name>
		<Age>younger than Paul</Age>
	</Array>
	<Array>
		<Name>Paul</Name>
		<Age>older than Tom</Age>
	</Array>
</Root>

Combine the Arrays

With all of that prep work done, we can begin to actually merge the arrays. See this Select action:

Power Automate Selection action which combines two separate arrays.

As you can see, all of the work is done in a single select action, but it requires a bit of explanation. The first part is straightforward:

  • The input for the select is Array1
  • The Keys Name and Country are drawn from Array1 using the item() syntax.
  • The Age key is pulled from the XML using the xpath expression.

The xpath expression is easier to read like this:

xpath
(
  outputs('XML'), 
  concat('//Array[Name/text()="', item()['Name'], '"]/Age/text()')
)?[0]

The concat function is used to dynamically create an xpath query by using the name from the current item. So in the first iteration of the loop the xpath expression would be:

//Array[Name/text()="Tom"]/Age/text()

What are we asking xpath to do here:

  • Find all of the elements within Array
  • Only find nodes within Array where Name is equal to Tom
  • Grab the text value of the Age where the Name matched.

You can test this out for yourself by copying the XML above and pasting it into xpather.com – then copy the expression above and you should see results like this:.

Image of results from xpath query on xpather.com

xpath will return an array of results, even if there is only a single match, so the final part of the expression is: [0] to grab the first result of the array.

So in a single select action we can combine the arrays easily. In our example we could simply it further by using the addProperty function in the select instead of key/value pairs.

An alternative select action which produces the same output in a simpler action.

In this example the Select action was changed to text mode instead key/value mode. Then I used the expression:

addProperty
(
  item(), 
  'Age', 
  xpath(outputs('XML'), concat('//Array[Name/text()="', item()['Name'], '"]/Age/text()'))?[0]
)

This simply takes the current item and adds a new property called Age and assigns the value returned from the xpath expression.

Conclusion

Which method should you use and is this method worth the hassle?

It depends on how performance dependant your flow is. If you are only working with a small amount of data, the method Tom shows is easier and will execute quickly.

If you are working with hundreds or thousands of rows of data, then this method will be much faster. It’s always fun to compare different methods of doing the same thing!

Filed Under: Power Platform Tagged With: Power Automate

Modify the BIOS of a Lenovo M92 to enable native NVMe support

July 5, 2022 by Paulie Leave a Comment

In this post I will show you how you can modify the BIOS of a Lenovo M92 so that it can support an NVMe drive as a native boot device. This is a similar procedure to a post I wrote on how to enable NVMe support on a Dell Optiplex 7010 last year. … [Continue reading] about Modify the BIOS of a Lenovo M92 to enable native NVMe support

Filed Under: How To Tagged With: NVMe

Sort an Array of Objects with Power Automate

July 4, 2022 by Paulie Leave a Comment

This post explains how to sort an array of objects in Power Automate. This method only works for objects that contain a numeric value on which to sort. I have done a previous post on How to Sort an Array in Power Automate using Office Scripts, … [Continue reading] about Sort an Array of Objects with Power Automate

Filed Under: Power Platform Tagged With: Power Automate

Add working days to a date in a Power Automate Flow

April 5, 2022 by Paulie 6 Comments

In this post I will show you a Power Automate Flow you can use to add days to a given date and exclude weekends, or specific dates from the result. I was inspired to do my own solution by some recent posts by others: Tom Riha - How to add only … [Continue reading] about Add working days to a date in a Power Automate Flow

Filed Under: Power Platform Tagged With: Power Automate

Install and boot from an NVMe SSD on a Dell OptiPlex 790 or 990

February 13, 2022 by Paulie 20 Comments

In this post I will show you how you can modify the BIOS of a Dell OptiPlex 790 or 990 so that it can support an NVMe SSD drive as a boot device. Although these machines have a UEFI BIOS, they do not contain an NVMe driver. By adding the driver … [Continue reading] about Install and boot from an NVMe SSD on a Dell OptiPlex 790 or 990

Filed Under: How To Tagged With: NVMe

  • Go to page 1
  • Go to page 2
  • Go to page 3
  • Interim pages omitted …
  • Go to page 35
  • Go to Next Page »

Primary Sidebar

Link to my LinkedIn Profile
Buy me a coffee

Recent Posts

  • How to merge arrays in Power Automate
  • Modify the BIOS of a Lenovo M92 to enable native NVMe support
  • Sort an Array of Objects with Power Automate
  • Add working days to a date in a Power Automate Flow
  • Install and boot from an NVMe SSD on a Dell OptiPlex 790 or 990

Recent Comments

  • Claire on Power Automate HTML Table Styling and Formatting
  • Manuela CarreƱo on How to create a Zip file in Power Automate for free
  • DEV on Power Automate HTML Table Styling and Formatting
  • Paulie on Install and boot from an NVMe SSD on a Dell OptiPlex 9020, 7020 or 3020
  • Jafet on Install and boot from an NVMe SSD on a Dell OptiPlex 9020, 7020 or 3020

Subscribe to Blog via Email

Enter your email address to subscribe to this blog and receive notifications of new posts by email.

Join 270 other subscribers.

Go to mobile version