Count number of cells with a value in each row of a table-CodePudding

I wish to count the number of blank cells within each cell of a column using PQ.

e.g.

There appears to be a count blank function but this counts the number of blank cells vertically. I wish to count the number of blank cells horizontally.

CodePudding user response：

Add index column (using default, that starts at zero)

Then add column ... custom column... with formula

= List.Sum(List.Transform(Record.ToList(Source{[Index]}), each if _=null then 1 else 0))

It converts the row into a record

Then converts the record into a list

Then transforms each item in the list to a 1 if null or 0 if not

Then takes the sum of that

Alternate:

= List.Count(Table.ColumnNames(Source))-List.NonNullCount(Record.ToList(Source{[Index]}))

In all instances, replace Source with the name of your actual prior step