Power Query Problems

How to Count Positive Values in Each Row with Power Query Custom Column

Phi Hung Vo Sep 30, 2026 869 views

Question details

The user needs to count the number of values greater than zero across multiple columns for each individual row using Power Query.

How to Count Positive Values in Each Row with Power Query Custom Column

Product: Excel
Device & OS: not provided
Scenario: Data transformation requires a dynamic row-by-row count of positive numbers without manually referencing every single column name.
Observed behavior: The user wants a dynamic M code solution to iterate through row values, skip non-numeric identifier columns, and return the count of positive numbers.

Before you start

Ensure your dataset is successfully loaded into the Power Query Editor and identify exactly how many initial identifier columns (such as ID or Name) need to be excluded from the numeric count.

Solution 1Recommended

Use M Code to Count Positive Values in a Custom Column

Create a custom column using a combination of List functions to convert row records into lists, filter positive values, and count them dynamically.

By converting each row into a list and skipping non-numeric identifier columns, you can apply a filter to isolate and count only the numbers greater than zero. This approach is highly dynamic and automatically adapts even if new numeric columns are added to your dataset later.

Open the Custom Column Dialog

In the Power Query Editor, navigate to the "Add Column" tab on the top ribbon and click on "Custom Column".

Name the New Column

In the Custom Column dialog box, enter a descriptive name for your new column, such as "Positive Value Count".

Enter the M Code Formula

In the Custom column formula box, enter the following code: List.Count(List.Select(List.Skip(Record.ToList(_), 1), each _ > 0))

Adjust the Skipped Columns

If your table has more than one identifier column (e.g., ID and Name), change the '1' in the List.Skip function to match the number of columns you want to ignore.

Apply and Save

Click "OK" to apply the formula. Your new column will now display the total count of positive numbers for each row.

Use M Code to Count Positive Values in a Custom Column

Understanding the Code: Record.ToList(_) converts the current row into a list. List.Skip ignores the specified number of starting columns. List.Select filters the remaining items for values greater than 0, and List.Count returns the total number of qualifying items.

Free Microsoft Office alternative

Try WPS Office for Your Data Analysis Needs

While Power Query is a highly specific feature for advanced data modeling, you might often only need straightforward ways to analyze and count your data. WPS Office provides a free, lightweight, and highly compatible alternative for everyday spreadsheet tasks, featuring built-in advanced formulas and familiar pivot tables without the steep learning curve.

1. Download WPS Office: Visit the official WPS website and download the free WPS Office suite.
2. Open Your Data File: Launch WPS Spreadsheet and open your existing Excel workbook natively without formatting loss.
3. Use Standard Formulas: Instead of complex M code, use standard array formulas or the COUNTIF function (e.g., =COUNTIF(B2:Z2, ">0")) to easily count values across rows.

Free to use with a lightweight, fast installation processFully compatible with Microsoft Excel (.xlsx, .xls, .csv) formatsFamiliar user interface with zero learning curve for Excel usersBuilt-in powerful functions like COUNTIF for easy data counting

Frequently Asked Questions

How can I change the formula to count values greater than a specific number, like 10?

You can modify the condition inside the List.Select function. Simply change 'each _ > 0' to 'each _ > 10' in your Custom Column formula.

What if I have multiple identifier columns to skip at the beginning of my table?

Adjust the numeric argument in the List.Skip function. For example, if you have 3 identifier columns (like ID, Name, and Category), change List.Skip(Record.ToList(_), 1) to List.Skip(Record.ToList(_), 3).

Can I count negative values instead of positive ones?

Yes, you can count negative values by changing the logical operator in the formula. Use 'each _ < 0' instead of 'each _ > 0'.

How can I achieve this row count in standard Excel or WPS Spreadsheet without Power Query?

In a standard spreadsheet interface, you can easily use the COUNTIF function. Typing =COUNTIF(B2:Z2, ">0") in a cell will count all positive values in row 2 from column B through Z.

Excel & Data Problems