cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
TCH
Level: Powered On

Count occurences

Hi there, 

I am in a bit of a pickle, i have a sharepoint list with a bunch of names in it, i now want to move these to an excel sheet with flow this is relatively easy. In my sharepoint list is the columns Name and Number, where the input to the table in excel  would correspond to these. However I have made a third column in my excel sheet in this i want to count the number of occurences of each name. 

What i am doing rightnow is using this expression in flow,

 

'length(body('Hent_elementer')?['value'])'

 

however this counts all of the values that have been imported, my question is whether there is a way for me to filter this so that it only counts specific names, 
so that if "Sophie" is there twice it counts 2 and if "Jack" is there once it counts 1 etc. 🙂 

Hope you can help 
Best Wishes Tobi 🙂 

 
1 ACCEPTED SOLUTION

Accepted Solutions
Dual Super User
Dual Super User

Re: Count occurences

Hey @TCH 

 

This is a rather complex/ long flow that I have. There might be easier and better ways too. Now that you already moved all the sharepoint items to excel, you can use :

First get items from sharepoint and create an array variable. Append the title (name) to this array variable and check if the curent list item exists in it. This way you can get all the unique names in the array. 

comp1.PNG

Now for each name in the array, list rows in excel table use the filter query and then based on that update all these rows with the count using the expression: 

length(body('List_rows_present_in_a_table')?['value'])

 

Comp2.PNG

Hope this Helps!

 

If this reply has answered your question or solved your issue, please mark this question as answered. Answered questions helps users in the future who may have the same issue or question quickly find a resolution via search. If you liked my response, please consider giving it a thumbs up. THANKS!

View solution in original post

21 REPLIES 21
Dual Super User
Dual Super User

Re: Count occurences

Hey @TCH 

 

This is a rather complex/ long flow that I have. There might be easier and better ways too. Now that you already moved all the sharepoint items to excel, you can use :

First get items from sharepoint and create an array variable. Append the title (name) to this array variable and check if the curent list item exists in it. This way you can get all the unique names in the array. 

comp1.PNG

Now for each name in the array, list rows in excel table use the filter query and then based on that update all these rows with the count using the expression: 

length(body('List_rows_present_in_a_table')?['value'])

 

Comp2.PNG

Hope this Helps!

 

If this reply has answered your question or solved your issue, please mark this question as answered. Answered questions helps users in the future who may have the same issue or question quickly find a resolution via search. If you liked my response, please consider giving it a thumbs up. THANKS!

View solution in original post

TCH
Level: Powered On

Re: Count occurences

 

hi @yashag2255 
Thanks for the great response, the issue that i am having at this moment is that althoug this seems like a valuable way of achieving this, the flow(although it says it runs to completion) doesn't actually update the rows?? Is this something you have seen before? 🙂 

Update row.PNG

Dual Super User
Dual Super User

Re: Count occurences

Hey @TCH 

 

Looks like the List rows in an excel is not returning anything. Can you please expand and share a screesnhot of that? 

 

Hope this Helps!

 

If this reply has answered your question or solved your issue, please mark this question as answered. Answered questions helps users in the future who may have the same issue or question quickly find a resolution via search. If you liked my response, please consider giving it a thumbs up. THANKS!

TCH
Level: Powered On

Re: Count occurences

Of course 🙂 

2509191.PNG

 

2509192.PNG

 

2509193.PNG

 

TCH
Level: Powered On

Re: Count occurences

@yashag2255   ^^ forgot to tag you 

Dual Super User
Dual Super User

Re: Count occurences

Hey @TCH 

 

In the filter query can you add single quotes for the current item in the List rows action? (type ' select the current item and then type ')

 

Also, in teh run history, can you check the array is getting populated or not?

 

Hope this Helps!

If this reply has answered your question or solved your issue, please mark this question as answered. Answered questions helps users in the future who may have the same issue or question quickly find a resolution via search. If you liked my response, please consider giving it a thumbs up. THANKS!

TCH
Level: Powered On

Re: Count occurences

@yashag2255 

Just tried it

Same issue, It still does "work" it just does not add/update the row that it should. 

The array is getting populated, however it only populates the first time the "subject" occurs meaning that as an example 

it populates the first time the name "John Lennon" is located by the flow. However, the next time this name occurs instead of populating it reads ActionBranchingConditionNotSatisfied , and says that it skipped this. 

But in my mind that is correct, it should only store the same value once, and then later on count the number of occurences of set value? 


Dual Super User
Dual Super User

Re: Count occurences

Hey @TCH 

 

Yep. That is how it should work. Now can you check if there are any items being returned in the Get items action? Did you add the single quotes '' to wrap the curent item in the filter query expression? Also, can you check if you are using the correct name of the column in the filter query? 

 

Hope this Helps!

If this reply has answered your question or solved your issue, please mark this question as answered. Answered questions helps users in the future who may have the same issue or question quickly find a resolution via search. If you liked my response, please consider giving it a thumbs up. THANKS!

TCH
Level: Powered On

Re: Count occurences

@yashag2255 

Items are definately being returned in the Get Items action, as it pulls thedata and adds these to an excel sheet. 
Yes i used single qutoes 🙂 

I am fairly certain it is the correct one, however i will check the two others, how do you use one that is multiple words withspaces between, so one is "total price" however, putting it in like this will result in a syntax error, is it not possible to use column names with spaces in them? 🙂 

Dual Super User
Dual Super User

Re: Count occurences

Hey @TCH 

 

Can you check if there are items being returned in the List rows action? (where you have the filter query?)

 

If there are spaces in the column name, you can do this total_x0020_price eq ''. 

 

Hope this helps!

If this reply has answered your question or solved your issue, please mark this question as answered. Answered questions helps users in the future who may have the same issue or question quickly find a resolution via search. If you liked my response, please consider giving it a thumbs up. THANKS!

TCH
Level: Powered On

Re: Count occurences

@yashag2255 

When i try replacing the space with _x0020_ i still error out and says that it can't find the column with that name? 🙂 

It does not necessery look like there is any output from the list_rows_in_a_table which obviously also would be the reason for the lack of updating? 🙂 

Output list rows.PNG

Dual Super User
Dual Super User

Re: Count occurences

Hey @TCH 

 

Yes! So the List rows in excel is not returning any value and that is why no items are getting updated. Can you check the column name in excel with which you want to filter the results? The column name should not have any special characters and the spaces should be filled by the string i mentioned earlier. 

 

Hope this Helps!

 

If this reply has answered your question or solved your issue, please mark this question as answered. Answered questions helps users in the future who may have the same issue or question quickly find a resolution via search. If you liked my response, please consider giving it a thumbs up. THANKS!

TCH
Level: Powered On

Re: Count occurences

@yashag2255 

This is very strange, i've tried to filter by all of the different collumns now, and non of them will return anything in List_rows_in_a_table? 🙂 

There are no special characters, and i have even made them so there are no spaces? 

Dual Super User
Dual Super User

Re: Count occurences

Hey @TCH 

 

Can you share some screenshots? 

 

Run history of the List rows excel action

Edit mode of the List rows action

 

 

TCH
Level: Powered On

Re: Count occurences

 

@yashag2255 

Yes of course 🙂 

Here is the edit mode: 

List rows edit.PNG

 

 

 

 

 

 

 



 

Here is the "run" mode: 


List row run.PNG

 

 

 

 

 

 

 

 

 

 

 

 

 

 

And here is the excel table it plots the data into, so the Prisforperiode field, is where it should plot the "occurences" into? 


Excel table.PNG

Dual Super User
Dual Super User

Re: Count occurences

Hey @TCH 

 

By any chance can you check if there are any spaces between the sinqle quotes and the current item atrribute in the filter query? 

 

Hope this Helps!

 

If this reply has answered your question or solved your issue, please mark this question as answered. Answered questions helps users in the future who may have the same issue or question quickly find a resolution via search. If you liked my response, please consider giving it a thumbs up. THANKS!

TCH
Level: Powered On

Re: Count occurences

@yashag2255 

That seemed to fix the list rows action, this is now giving and output however, now it is erroring on the update a row action, see below 🙂 

 

 

Update a row fail 1.PNG

 

 

 

 

 

 

 

 

 

 

 

 

 

Update a row fail 2.PNG

 

Dual Super User
Dual Super User

Re: Count occurences

Hey @TCH 

 

That's some progress. Can you post a screenshot of the Update row action in edit mode? We need to check if you have provided the right references to the excel sheet and the table, row and then finally the key values. 

 

Hope this Helps!

If this reply has answered your question or solved your issue, please mark this question as answered. Answered questions helps users in the future who may have the same issue or question quickly find a resolution via search. If you liked my response, please consider giving it a thumbs up. THANKS!

TCH
Level: Powered On

Re: Count occurences

@yashag2255 

Yes of course 🙂 

Here goes: 

Update a row edit.PNG

Helpful resources

Announcements
thirdimage

Power Automate Community User Group Member Badge

Fill out a quick form to claim your user group badge now!

firstImage

Incoming: New and improved badges!

We've given our badges an overhaul and also added some brand new ones!

fifthimage

Microsoft Learn

Learn how to build the business apps that you need.

sixthImage

Power Platform World Tour

Find out where you can attend!

seventhimage

Webinars & Video Gallery

Watch & learn from the Power Automate Community Video Gallery!

Top Kudoed Authors (Last 30 Days)
Users online (5,507)