Power Query Problems

Count Non-Blank Cells Across Columns in Excel Power Query

Tauseeq MagsiTauseeq Magsi Nov 18, 2026 869 views

Question details

Find a Power Query M equivalent of Excel's COUNTA function for counting non-empty values across multiple columns.

Count Non-Blank Cells Across Columns in Excel Power Query
Product
Microsoft Excel Power Query
Device & OS
not provided
Scenario
Add a count for each row across selected columns in a Power Query table.
Observed behavior
The desired result is a per-row count of populated values, with a choice of whether to include empty text strings.
Before you start

Open the query in Power Query Editor and check the exact names of the columns you want to count. Decide whether empty text strings should count as values or be excluded along with nulls.

Solution 1Recommended

Count non-null values with List.NonNullCount

Use this method to count non-null values across specified columns in each row.

1
Open the query

In Excel, select Data > Queries & Connections, then double-click the query to open Power Query Editor.

2
Add a custom column

In Power Query Editor, select Add Column > Custom Column and enter a name such as Non-null count.

3
Enter the M formula

In the Custom column formula box, enter `List.NonNullCount({[A], [B], [C], [D], [E], [F], [G], [H], [I], [J]})`. Replace A through J with your actual column names, then select OK.

4
Load the result

Check the counts in the new column, then select Home > Close & Load to return the results to Excel.

Count non-null values with List.NonNullCount
Empty strings are counted: List.NonNullCount excludes null values but includes empty text strings (`""`). Use the second solution if empty strings should also be excluded.
Free Microsoft Office alternative

Count populated cells in WPS Spreadsheets

WPS Office is a free, lightweight alternative with a familiar spreadsheet interface and Excel format compatibility for an easy transition. Use COUNTA in WPS Spreadsheets to count populated cells in a worksheet; it does not replace the Power Query M solution.

Count populated worksheet cells with the familiar COUNTA function.Open and edit Microsoft Excel XLSX and XLS files.Navigate a familiar interface designed for everyday spreadsheet work.Use a free, lightweight office suite for common document tasks.
microsoft office alternative - wps office

Frequently Asked Questions

What is the Power Query equivalent of COUNTA across columns?

In Add Column > Custom Column, use `List.NonNullCount({[A], [B], [C]})` and replace the column names with yours. It counts values that are not null, including empty text strings.

How do I exclude empty strings as well as nulls?

Use `List.Count(List.Select({[A], [B], [C]}, each _ <> null and _ <> ""))` in a custom column. This excludes both null and empty text values.

Will zeros and false values be counted?

Yes. Both formulas count zero and false because neither is null or an empty text string.

Can I count populated cells without Power Query?

In an Excel or WPS Spreadsheets worksheet, enter `=COUNTA(A2:J2)` in a cell to count non-empty cells across that row. COUNTA includes cells containing formulas that return an empty string.