cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
xr19970428
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?

1 REPLY 1
camargos88
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:

NAME, ATTRIBUTE, VALUE

 

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

 

Unpivot columns (Power Query) - Excel (microsoft.com)



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

Proud to be a Super User!



Helpful resources

Announcements
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