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

Count occurrences of specifc text in a SharePoint column

I have a couple of SharePoint column which via various Flows (or should I say Power Automates!?) get their values appended by a user's display name (e.g. John Doe) and a semi-colon (;).

 

The SharePoint columns are called 'AddedBy' and 'DeletedBy'.

 

I want to be able to count the number of occurrences of the current user's display name (separately) in each of the two columns.

 

I am using User().FullName to get the current user's display name (e.g. John Doe) but I am having trouble counting how many times this occurs in the two columns i.e. 'AddedBy' and 'DeletedBy'.

 

Let's say I'm logged in as John Doe and the data in the SharePoint list looks like this:

 

AddedBy   DeletedBy
John Doe; John DoeJohn Doe

 

I want to be able to count how many times John Doe exists in each column, so I should get the result 2 (AddedBy), 1 (DeletedBy). Happy for the values to be shown via a label if that's easiest.

 

The reason for this is that once I have the count values, I can say that if the AddedBy count value is greater than the DeletedBy count value, do something else (yet to be determined).

 

Bit of a strange request but hoping someone can help!

17 REPLIES 17
PowerAddict
Level 10

Re: Count occurrences of specifc text in a SharePoint column

These are 2 collections I created to test out your scenario:
ClearCollect(AddedList, {AddedBy: "John Doe"}, {AddedBy: "John Doe"}, {AddedBy: "Hardit Bhatia"});
ClearCollect(DeletedList, {DeletedBy: "John Doe"}, {DeletedBy: "Hardit Bhatia"}, {DeletedBy: "Hardit Bhatia"})

I then used two labels to display the count of the number of times John Doe exists in each of the two rows, using the following formula:

CountIf(AddedList, AddedBy = "John Doe")
CountIf(DeletedList, DeletedBy = "John Doe")

Instead of John Doe, you can use User().FullName

Let me know if this doesn't work for you.

---
If you like this reply, please give kudos. And if this solves your problem, please accept this reply as the solution. Thanks!

Hardit Bhatia
https://www.thepoweraddict.com
Joe_Fox
Level: Powered On

Re: Count occurrences of specifc text in a SharePoint column

Thanks Hardit,

I'm not sure how to implement this as the source is the SharePoint columns (AddedBy and DeletedBy).

Would I need to 'collect' the data in each of these fields somehow before using the CountIf(AddedList, AddedBy = "John Doe")?

 

PowerAddict
Level 10

Re: Count occurrences of specifc text in a SharePoint column

You can just use your SharePoint list name instead of the collection name. That being said, you can technically collect all of your SharePoint data in a collection. I am assuming these 2 columns are columns of a SharePoint list?

If not, let me know how these columns are structured?

---
If you like this reply, please give kudos. And if this solves your problem, please accept this reply as the solution. Thanks!

Hardit Bhatia
https://www.thepoweraddict.com
Joe_Fox
Level: Powered On

Re: Count occurrences of specifc text in a SharePoint column

Thanks again Hardit,

Yes, both columns are just SharePoint text columns.

Still not quite sure which element of the PowerApp I should be putting the SharePoint list name.

I have the two labels for the count results, but not sure where the collection would be.

 

PowerAddict
Level 10

Re: Count occurrences of specifc text in a SharePoint column

Say you have two labels. One in which you want to show the count of how many times the current user's name appears in the AddedBy column and another one to display the count of how many times the current user's name appears in the DeletedBy column.

I am assuming that these 2 columns are part of a SharePoint list called List1.

In that case, the Text property of the first label will be:

CountIf(List1, AddedBy = User().FullName)

And the Text property of the second label will be:

CountIf(List1, DeletedBy = User().FullName)

Let me know if this works.

---
If you like this reply, please give kudos. And if this solves your problem, please accept this reply as the solution. Thanks!

Hardit Bhatia
https://www.thepoweraddict.com
Joe_Fox
Level: Powered On

Re: Count occurrences of specifc text in a SharePoint column

This gets me close to what I'm after, but this counts all of the occurrences of the entire list, whereas what I want to do is count the occurrences in a particular row of the list. For example, if the list looks like this:

 

AddedByDeletedBy

John Doe

John Doe
John Doe;John Doe;John Doe;John Doe
John DoeJohn Doe;John Doe;

 

...in the PowerApp the gallery would show AddedBy values of 1,3 and 1 and DeletedBy values of 1,1 and 3.

 

Presuming I'm logged in as John Doe, the label with 'Text' property of CountIf('SharePoint List Name', AddedBy = User().FullName) would just show 2 and 2, which isn't quite what I want. I think I need to use a 'contains' rather than an = in the condition too, which I'm struggling with!

PowerAddict
Level 10

Re: Count occurrences of specifc text in a SharePoint column

Got it, makes sense now. Let me try and I will get back to you.

Thanks,
Hardit Bhatia
https://thepoweraddict.om
Highlighted
WillPage
Level 8

Re: Count occurrences of specifc text in a SharePoint column

I think your solution is to use AddColumns(), CountIf() and Split() together for this. I'll make an assumption that your SP List name is 'SP List'.

 

AddColumns('SP List', "AddedByCount", CountIf(Split(AddedBy, "; "), Result = User().FullName), "DeletedByCount", CountIf(Split(DeletedBy, "; "), Result = User().FullName))

 

What this is doing is adding a a pair of columns to the table in the items property of your control (gallery, data table, combo box etc). Each column is the count of the number of columns in the table formed by splitting the AddedBy and DeletedBy strings on "; " into tables where the row matches the full name of the current user.

 

I haven't tested this, I just typed it out into the forum so E&OE etc. I can't remember of the name of the column that comes out of the Split() function is Result or Value, but the formula above I've used Result.

 

These new columns will be available just like any other i.e. ThisItem.AddedByCount in a gallery, but only in the control for which the Items property contains this formula. To use it across several controls, create a collection when the app loads or some other way, like OnVisible, OnSelect or OnChange of a screen or control.

 

Be aware that AddColumns() isn't delegable (despite there being no warning about it) so if your data source exceeds the limit set in the app's advanced properties, you will need to pull a delegable query into a collection then run the AddColumns on that.

PowerAddict
Level 10

Re: Count occurrences of specifc text in a SharePoint column

@Joe_Fox the solution provided by @WillPage should work perfectly fine. You beat me to it 🙂

 

To provide an explanation, Split function breaks a string into a table of substrings using the specified delimiter. So in the case of John Doe;John Doe the count of the table will be 2 rows. 

 

The only modification to the formula will be: 

 

AddColumns('SP List', "AddedByCount", CountIf(Split(AddedBy, ";"), Result = User().FullName), "DeletedByCount", CountIf(Split(DeletedBy, ";"), Result = User().FullName))

 

Thanks,

Hardit Bhatia

https://thepoweraddict.com

Helpful resources

Announcements
thirdimage

Power Apps Community User Group Member Badge

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

sixthImage

Power Platform World Tour

Find out where you can attend!

Power Platform 2019 release wave 2 plan

Power Platform 2019 release wave 2 plan

Features releasing from October 2019 through March 2020

SecondImage

Difinity Conference

The largest Power BI, Power Platform, and Data conference in New Zealand

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