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:
2
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/pricing/
If you don’t want to use that, you’ll need to loop and do the hard work that comes with that approach.
5
is it neccessary to stick with xml and xpath? What if you make an array from your input, then find the starting and the ending position of your string, then make an array of your result strings?
(for example: “Data and Analytics|2024-09;2024-09-30;”: you need the substring between 19th and 27th characters, and your result would be “2024-09” ).
where:
-
‘Apply to each’:
variables(‘varArray’)
-
‘Compose – Start’:
add(indexOf(item(),’|’),1)
-
‘Compose – Length’:
substring(item(),outputs(‘Compose_-Start’),outputs(‘Compose-_Length’))
-
Append to array variable:
outputs(‘Compose’)
1