Need assistance with Xpath expression to count non-null values after a delimiter

Question

I have tried to make the following expression work in a power automate flow without luck. The objective of this expression is to count all the non-null values after the second ";" for each substring before "|" , AND each substring between "|" and the first ";" such as "2024-09" from the data sample.

xpath(
    outputs('Compose_3'),
    concat(
        'count(//root/item[starts-with(., "', 
        first(split(item(), '|')), 
        '") and normalize-space(substring-after(substring-after(., ";"), ";")) != "" and contains(., ";', 
        substring-before(substring-after(item(), "|"), ";"), 
        '")])'
    )
)

Here is a sample input for this xpath:

"Data and Analytics|2024-09;2024-09-30;",
"Data and Analytics|2024-09;2024-09-30;",
"Enterprise App and Emerging Tech|2024-09;2024-10-01;",
"Enterprise App and Emerging Tech|2024-09;2024-10-01;",
"Mobility and Customer Experience|2024-09;2024-10-07;2024-12-31",
"Mobility and Customer Experience|2024-09;2024-10-07;2024-12-31",
"Mobility and Customer Experience|2024-10;2024-10-27;2024-11-07",
"Data and Analytics|2024-10;2024-10-23;2024-12-08",
"Data and Analytics|2024-10;2024-10-23;2024-12-08",
"Cloud - Infra - CS and Obser|2024-10;2024-10-24;2024-12-15"

---

EDIT by @Fravadona

XML data, based on the comment and the question:

<root>
  <item>Data and Analytics|2024-09;2024-09-30;</item>
  <item>Data and Analytics|2024-09;2024-09-30;</item>
  <item>Enterprise App and Emerging Tech|2024-09;2024-10-01;</item>
  <item>Enterprise App and Emerging Tech|2024-09;2024-10-01;</item>
  <item>Mobility and Customer Experience|2024-09;2024-10-07;2024-12-31</item>
  <item>Mobility and Customer Experience|2024-09;2024-10-07;2024-12-31</item>
  <item>Mobility and Customer Experience|2024-10;2024-10-27;2024-11-07</item>
  <item>Data and Analytics|2024-10;2024-10-23;2024-12-08</item>
  <item>Data and Analytics|2024-10;2024-10-23;2024-12-08</item>
  <item>Cloud - Infra - CS and Obser|2024-10;2024-10-24;2024-12-15</item>
</root>

Here are the desired results based on the data above:

Answer 1

Based on the updates you've provided, I've rewritten my answer.

Grouping the data is not possible in XPath alone. The following will do it for you though and do it easily.

Compose Data

This is your data as an array. I'm making the assumption that you have that somewhere in your flow.

[
  "Data and Analytics|2024-09;2024-09-30;",
  "Data and Analytics|2024-09;2024-09-30;",
  "Enterprise App and Emerging Tech|2024-09;2024-10-01;",
  "Enterprise App and Emerging Tech|2024-09;2024-10-01;",
  "Mobility and Customer Experience|2024-09;2024-10-07;2024-12-31",
  "Mobility and Customer Experience|2024-09;2024-10-07;2024-12-31",
  "Mobility and Customer Experience|2024-10;2024-10-27;2024-11-07",
  "Data and Analytics|2024-10;2024-10-23;2024-12-08",
  "Data and Analytics|2024-10;2024-10-23;2024-12-08",
  "Cloud - Infra - CS and Obser|2024-10;2024-10-24;2024-12-15"
]

Select Enhanced Data

This step prepares the data for the next action. It essentially breaks out the string into a more workable form. This is the definition of the Select ...

{
  "type": "Select",
  "inputs": {
    "from": "@outputs('Compose_Data')",
    "select": {
      "Portfolio": "@first(split(item(), '|'))",
      "Period": "@first(split(last(split(item(), '|')), ';'))",
      "Count": "@if(endsWith(item(), ';'), 0, 1)"
    }
  },
  "runAfter": {
    "Compose_Data": [
      "Succeeded"
    ]
  }
}

The above produces this result ...

[
  {
    "Portfolio": "Data and Analytics",
    "Period": "2024-09",
    "Count": 0
  },
  {
    "Portfolio": "Data and Analytics",
    "Period": "2024-09",
    "Count": 0
  },
  {
    "Portfolio": "Enterprise App and Emerging Tech",
    "Period": "2024-09",
    "Count": 0
  },
  {
    "Portfolio": "Enterprise App and Emerging Tech",
    "Period": "2024-09",
    "Count": 0
  },
  {
    "Portfolio": "Mobility and Customer Experience",
    "Period": "2024-09",
    "Count": 1
  },
  {
    "Portfolio": "Mobility and Customer Experience",
    "Period": "2024-09",
    "Count": 1
  },
  {
    "Portfolio": "Mobility and Customer Experience",
    "Period": "2024-10",
    "Count": 1
  },
  {
    "Portfolio": "Data and Analytics",
    "Period": "2024-10",
    "Count": 1
  },
  {
    "Portfolio": "Data and Analytics",
    "Period": "2024-10",
    "Count": 1
  },
  {
    "Portfolio": "Cloud - Infra - CS and Obser",
    "Period": "2024-10",
    "Count": 1
  }
]

Aggregate

Now you need to use this handy little operation from the Advanced Data Operations connector. It performs a GROUP BY on the data you give it using the aggregation operation of your choice.

You can see how that step is defined in the original image above.

That will produce this result which is in line with your expectations ...

[
  {
    "Portfolio": "Cloud - Infra - CS and Obser",
    "Period": "2024-10",
    "Count": 1
  },
  {
    "Portfolio": "Data and Analytics",
    "Period": "2024-09",
    "Count": 0
  },
  {
    "Portfolio": "Data and Analytics",
    "Period": "2024-10",
    "Count": 2
  },
  {
    "Portfolio": "Enterprise App and Emerging Tech",
    "Period": "2024-09",
    "Count": 0
  },
  {
    "Portfolio": "Mobility and Customer Experience",
    "Period": "2024-09",
    "Count": 2
  },
  {
    "Portfolio": "Mobility and Customer Experience",
    "Period": "2024-10",
    "Count": 1
  }
]

https://statesolutions.com.au/aggregate/

https://statesolutions.com.au/pricing/

If you don't want to use that, you'll need to loop and do the hard work that comes with that approach.