Forum Discussion
Counting NonBlank Cells across multiple columns
Hello House,
I have a dataset where I used "SplitColumn by Delimeter" to split a particular column into several columns. Now, I will like to have a calculated column that will count the number of cells with non-blank for each row.
Thank you.
6 Replies
- Greg_Deckler
Community Champion
Anonymous I would recommend unpivoting those columns in Power Query. However, if for some reason you need them in multiple columns, you could use MC Aggregations:
MC Count Blank = VAR tmpCol1 = SELECTCOLUMNS(Data,"Column",[Column1]) VAR tmpCol2 = SELECTCOLUMNS(Data,"Column",[Column2]) VAR tmpCol3 = SELECTCOLUMNS(Data,"Column",[Column3]) VAR tmpCol4 = SELECTCOLUMNS(Data,"Column",[Column4]) VAR tmpTable = UNION(UNION(UNION(tmpCol1,tmpCol2),tmpCol3),tmpCol4) VAR tmpValue = COUNTROWS(FILTER(tmpTable,ISBLANK([Column])) RETURN tmpValueThank you very much for your quick response. This is appreciated.
I actually do need the splitting in the multiple columns hence this request.
When I copied and edited the syntax you suggested, it gave me some error. Please see the screenshot below
I think the error is from second to last step of the formula (ISBLANK([Column]))):
MC Count Blank =VAR tmpCol1 = SELECTCOLUMNS(MAD,"Column",[Please mention all the food your child ate between yesterday and now, Day&Nigh.1])VAR tmpCol2 = SELECTCOLUMNS(MAD,"Column",[Please mention all the food your child ate between yesterday and now, Day&Nigh.2])VAR tmpCol3 = SELECTCOLUMNS(MAD,"Column",[Please mention all the food your child ate between yesterday and now, Day&Nigh.3])VAR tmpCol4 = SELECTCOLUMNS(MAD,"Column",[Please mention all the food your child ate between yesterday and now, Day&Night.])VAR tmpTable = UNION(UNION(UNION(tmpCol1,tmpCol2),tmpCol3),tmpCol4)VAR tmpValue = COUNTROWS(FILTER(tmpTable,ISBLANK([Column]))RETURN tmpValue- Greg_Deckler
Anonymous I'm on my phone so hard to see error message. But may try
COUNTROWS(FILTER(tmpTable,[Column]=BLANK()))
