How to Aggregate Data in Power Automate Using XPath

In Power Automate, data transformation can sometimes be a challenging task, especially when dealing with large datasets. However, XPath can be a powerful ally in this process, particularly when you need to aggregate data for document generation or API calls. In this tutorial, we will explore how to use XPath to aggregate data efficiently, avoiding the performance penalties associated with using multiple actions and loops.

Prerequisites

Before we dive in, ensure you have a basic understanding of the XML format. If you're not yet familiar with it, you can quickly get up to speed with a good tutorial like this.

Getting Started with XPath

XPath is an expression syntax that allows you to query data in XML format. It's quite easy to learn, especially if you have a basic understanding of XML. Let's delve into an example to understand how to use XPath in Power Automate.

Example: Aggregating Football Player Data

Imagine we have a list of football players, as shown below:

We aim to transform this table into the following format, grouping data by the "Team" column and filtering rows to only include "Captain" and "Goalkeeper" positions:

Step-by-Step Guide to Data Transformation

Step 1: Convert Data to XML

First, we need to convert the data to XML to utilize XPath. The following Compose action helps us achieve this, creating a root element named "players":

- Compose
  Name: ComposeFootballPlayersXml
  Inputs: xml(json(concat('{"players":{"p":[', join(body('FootballPlayers'), ','), ']}}')))

Step 2: Aggregate Teams

Next, we aggregate the teams using the unique name of teams. The following action demonstrates how to do this:

- Compose
  Name: ComposeFootbalTeamsArray
  Inputs: union(xpath(ComposeFootballPlayersXml,'/players/p/rn_team/text()', createArray(null))

Step 3: Select and Aggregate Data

Finally, we use the Select action to aggregate the data of captains and goalkeepers:

- Select
  Name: AggregateCaptainsAndGoalkeepers
  From: take(outputs('ComposeFootballTeamsArray'), sub(length(outputs('ComposeFootballTeamsArray')), 1))
  Map:
    - Team: item()
    - Captain: xpath(outputs('ComposeFootballPlayersXml'), concat('/players/p[rn_team/text()=''', item(), ''' and rn_role = ''Captain'']/rn_name/text()'))[0]
    - Goalkeeper: xpath(outputs('ComposeFootballPlayersXml'), concat('/players/p[rn_team/text()=''', item(), ''' and rn_role = ''Goalkeeper'']/rn_name/text()'))[0]

This action iterates through the array of teams we built earlier, running two XPath queries for each team to find the captain and the goalkeeper.

Troubleshooting

Writing XPath queries, although seemingly simple, can sometimes be challenging, especially when using the small textbox in Power Automate actions. Here's how you can debug XPath queries more easily:

Common Issues

• Writing XPath queries are not easy in the tiny text box of Power Automate's actions.
• You won't see the result of any of the queries until you run the whole flow.
• Little typos and other syntax errors are not easy to find.

Debugging Tools

You can use the following tools to debug XPath queries:

• Notepad++ and its XML Tools plugin.
• Sublime and any of its many xpath or xml plugins.
• VSCode and its Delta XML plugin
• Select-Xml command in PowerShell
• A few lines of code!

// This is just one way of executing an XPath query in C#.
var doc = new XPathDocument("c:\\books.xml");
var nav = doc.CreateNavigator();
var result = nav.SelectSingleNode("/Cell/CellContent/Para/ParaLine/String/text()").Value

Debugging Example

To debug an XPath query or write it from scratch, test your flow once to generate the necessary XML. You can then copy this XML into your favorite editor and start writing your XPath queries.

As you see, in the above screenshot, an action has failed due to an error in our XPath query. We can find the generated XML in the "ComposeFootballPlayersXML" action, before it.

In the following video, I demonstrate how to use Sublime Text and its "xpath" plugin to debug an XPath query:

Conclusion

In this tutorial, we explored how to aggregate data in Power Automate using XPath, a powerful tool for data transformation. Remember, practice makes perfect. Keep experimenting with different queries and make use of the debugging tools mentioned to hone your skills further.