Counting occurrence of a value in Power Automate Flow

(0) Share

Report

Posted on by MrAutomate

206

In a flow, after my get items step, I am looking to count the total times a SharePoint list value occurs:

SPList Name = VehicleInventory

SPColumn Name = VehicleModel

Column Values (as of this moment) Volvo(9) Ford (3) etc etc

Without manually creating the variables up front for each vehicle type (avoid future flow maintenance) and doing an increment step, how can I do this in FLow?

I am stuck on 'composing' in the flow that will return a count value of each VehicleModel, and appreciate the assist!

End goal is to pop into my HTML table something as follows:

Volvos in the Fleet - 9

Fords in the Fleet -3

Kias in the Fleet -2

Many thanks!

Categories:

Building flows

I have the same question (0)

All responses (5)

Answers (2)

Verified answer

Paulie78 8,422 Moderator on at

Like (0)

Report

Check out this video I did for someone else very recently. It demonstrates a very efficient method:

https://youtu.be/v8B3zZDvs5g

Was this reply helpful? Yes No
MrAutomate 206 on at

Like (0)

Report

Paulie78, Many thanks for the video.

Way above my understanding , I am afraid .. In your example, you predefined the 1st step as an array, is that right and if so, the values can be 1 of 26 letters in the alphabet? Problem is, I can't predict what makes we may add to the fleet in the future right ? (also, not really wanting to manually build an array of potential vehicles )

Does anyone in the community have a simple solution to accomplish this using the get items of the vehicles we currently have?

I would think counting an occurrence would not be this difficult, but all posts I have seen seem to involve complicated steps... Thanks all...

Was this reply helpful? Yes No
Verified answer

v-bofeng-msft on at

Like (0)

Report
Hi @MrAutomate :

@Paulie78 does provide an excellent solution. The variable defined at the beginning just defines a data source for testing. On your side, you can directly use get items to obtain data without defining such a variable.

Here is the detailed steps, if it helps, please consider marking @Paulie78 's answer as solution.

concat(item()['VehicleModel'],'in the Fleet - ',length(xpath(xml(outputs('Compose')),concat('//Array[VehicleModel/text()="',item()['VehicleModel'],'"]'))))

The Result

Best Regards,

Bof

Was this reply helpful? Yes No
AJL90 2 on at

Like (0)

Report

Hi, I have tried both versions of these solutions and both times I hit the an error on the final step with xpath saying that it is invoking 4 parameters instead of the expected 2. It works if you hardcode the search term in the xpath query, so it seems to be an issue with invoking the "current item" term which it keeps failing on. Any help would be appreciated.

The execution of template action 'Select_2' failed: The evaluation of 'query' action 'where' expression '@length(xpath(xml(outputs('Compose')),'//Array[RegisteredSchool/text()="',item()['RegisteredSchool'],'"]'))' failed: 'The template language function 'xpath' expects two parameters: an XML object and an XPath expression. The function was invoked with '4' parameter(s). Please see https://aka.ms/logicexpressions#xpath for usage details.'.

Was this reply helpful? Yes No
takolota1 4,974 Moderator on at

Like (0)

Report

@AJL90

You could also try this template which includes a count of the number of records for each group as a parameter on each record. It doesn’t use any xpath.

https://powerusers.microsoft.com/t5/Power-Automate-Cookbook/Get-Distinct-Records-amp-Counts/td-p/2191533

Was this reply helpful? Yes No