Unanswered

Count occurrences of specifc text in a SharePoint column

(0) Share

Report

Posted on by Joe_Fox

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 Doe	John 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!

Categories:

Building Power Apps

I have the same question (0)

All responses (17)

Answers (0)

Sort by

PowerAddict 7,316 Most Valuable Professional on at

Like (0)

Report
Copy link

Link copied!

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

Was this reply helpful? Yes No
Joe_Fox 67 on at

Like (0)

Report
Copy link

Link copied!

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")?

Was this reply helpful? Yes No
PowerAddict 7,316 Most Valuable Professional on at

Like (0)

Report
Copy link

Link copied!

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

Was this reply helpful? Yes No
Joe_Fox 67 on at

Like (0)

Report
Copy link

Link copied!

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.

Was this reply helpful? Yes No
PowerAddict 7,316 Most Valuable Professional on at

Like (0)

Report
Copy link

Link copied!

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

Was this reply helpful? Yes No
Joe_Fox 67 on at

Like (0)

Report
Copy link

Link copied!

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:

AddedBy DeletedBy
John Doe
John Doe
John Doe;John Doe;John Doe; John Doe
John Doe John 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!

Was this reply helpful? Yes No
PowerAddict 7,316 Most Valuable Professional on at

Like (0)

Report
Copy link

Link copied!

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

Thanks,
Hardit Bhatia
https://thepoweraddict.om

Was this reply helpful? Yes No
WillPage 2,337 Super User 2026 Season 1 on at

Like (2)

Report
Copy link

Link copied!
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.

Was this reply helpful? Yes No
PowerAddict 7,316 Most Valuable Professional on at

Like (1)

Report
Copy link

Link copied!
@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

Was this reply helpful? Yes No
WillPage 2,337 Super User 2026 Season 1 on at

Like (1)

Report
Copy link

Link copied!
If you're unsure whether there's any whitespace around the semicolon, split on the semicolon only, like in @PowerAddict's example , then trim the result of Split() with TrimEnds():
AddColumns('SP List', "AddedByCount", CountIf(Split(AddedBy, ";"), TrimEnds(Result) = User().FullName), "DeletedByCount", CountIf(Split(DeletedBy, ";"), TrimEnds(Result) = User().FullName))

Was this reply helpful? Yes No