Counting empty columns in a sharepoint list

(0) Share

Report

Posted on by maxm4065

Hi, I'm wanting to count the number of empty cells in a SharePoint list row.

eg a sharepoint list has 15 columns, how can I count:

- how many columns there are in total? (15)

- how many haven't been filled in? (say, 4)

- or, how many have been filled in? (say, 11)

is there a way of doing this?

Categories:

Building Power Apps

All responses (4)

Answers (1)

Sort by

dinusc Microsoft Employee on at

Like
a
(0)

Report
Copy link

Link copied!

Hello @maxm4065

Since you're binding to a specific list, you should already now the number of total columns in the bound list. When editing the list (adding or removing columns), you will most likely have to edit the application as well. For finding the number of empty cells in a certain row, the following example should work. Note that my example is with 2 rows and 4 cells in order to minimize the formula length and to improve readability:
1. Add a button (Button1) and a label (Label1) to your app.
2. Button1.OnSelect=ClearCollect(coll, {c1:"V11",c2:"V12",c3:"",c4:"V14"},{c1:"V21",c2:"V22",c3:"V23",c4:"V24"})
3. Label1.Text="First row has " & First(ForAll(coll, {cnt1:If(IsBlank(c1),1,0)+If(IsBlank(c2),1,0)+If(IsBlank(c3),1,0)+If(IsBlank(c4),1,0)})).cnt1 & " empty value(s)"
4. Click on the button.

In this example, the label should display "First row has 1 empty value(s)"
maxm4065 5 on at

Like
a
(0)

Report
Copy link

Link copied!

thanks @dinusc.

I was hoping for a solution that I can use across multiple lists without having to rewrite it each time.

I had thought of something to your solution using a calculated column in the sharepoint list itself but for multiple lists with lots of columns each, this becomes a bit tedious...
Verified answer

dinusc Microsoft Employee on at

Like
a
(2)

Report
Copy link

Link copied!

I don't think this can be currently done from PowerApps. This looks like a very helpfull to function to have though. I would suggest to add it to the list of ideas: https://powerusers.microsoft.com/t5/PowerApps-Ideas/idb-p/PowerAppsIdeas
Adena 2 on at

Like
a
(0)

Report
Copy link

Link copied!

I used a calculated column and inserted a formula like this:
=count([column_name],"",[column_2],"")
continue with all columns you want to add.. and set the data return to number