Showing results for 
Search instead for 
Did you mean: 
New Member

How to calculate the number of non-blanks in each row?

Screenshot 2020-12-09 094334.png


Hi, I have a table with 27 colums with one called last name and 26 questions, like shown in picture. Since someone only answer some of questions and leave the blank for others. I want to calculate how many questions a person answer in a new column. How can I do that?

Super User III
Super User III

@xr19970428 ,


You can unpivot the values using Power Query.

Select the name column and right click -> unpivot other columns, the result is:



With this dataset you can easily count the blank values or nonblank values.


Unpivot columns (Power Query) - Excel (

Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Helpful resources

PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group!

MBAS on Demand

Microsoft Business Applications Summit sessions

On-demand access to all the great content presented by the product teams and community members! #MSBizAppsSummit #CommunityRocks

MBAS Attendee Badge

Claim Your Badge & Digital Swag!

Check out how to claim yours today!

Top Solution Authors
Top Kudoed Authors