Power Query Problems

How to Count Specific Values Across Columns in Power Query

Maira Mehtab Sep 20, 2026 868 views

Question details

The user needs to calculate how many times the word "Completed" appears across all columns for each individual row in Power Query, without having to hardcode specific column names.

Product: Power Query / Microsoft Excel
Device & OS: not provided
Scenario: Data transformation and summarization where a specific text value needs to be counted across a dynamic or unknown number of columns in a dataset.
Observed behavior: The goal is to implement a dynamic M code formula that evaluates the entire row as a record, correctly tallying the target string across all columns while gracefully handling potential column errors.

Before you start

Ensure that your source data does not contain any column-level errors before applying these formulas. If there are existing errors in the source columns, the row calculation will fail, so correct them or replace errors with null values first.

Solution 1Recommended

Use Record.ToList with List.Select (Recommended)

This method converts the current row into a flat list and filters it to count only the items that exactly match your target text. It is robust and ignores column names.

By utilizing the underscore (_) operator, Power Query captures the entire current row as a single record. Converting this record to a list allows you to use standard list operations to count occurrences regardless of how many columns are added or removed in the future.

Open the Custom Column Dialog

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

Enter the List.Select Formula

In the Custom Column formula box, input the following formula exactly: List.Count(List.Select(Record.ToList(_), each _ = "Completed"))

Apply and Verify

Click 'OK' to create the column. A new column will appear showing the total count of the word 'Completed' for each row.

Dynamic Column Support: Because this formula references the entire row instead of specific headers, it automatically adapts if your data source adds new columns later.

Solution 2

Use List.PositionOf or Text.PositionOf Alternatives

If you are working with specific text combinations or prefer an alternative logic, you can use position-based list functions to count occurrences.

Free Microsoft Office alternative

Looking for a Simpler Way to Analyze Data?

While Power Query offers advanced data transformation features, many data counting tasks can be solved effortlessly using standard formulas. WPS Office offers a free, lightweight, and highly compatible alternative to Microsoft Office, complete with familiar spreadsheet interfaces and powerful built-in functions to handle your data analysis needs without complex M coding.

1. Download WPS Office: Visit the official WPS website to download and install the free office suite.
2. Open Your Excel File: Launch WPS Spreadsheets and directly open your existing workbook with full formatting preserved.
3. Analyze Data Easily: Use simple formulas like =COUNTIF(A2:Z2, "Completed") to achieve the same result without using Power Query.

Fully compatible with Microsoft Excel (.xlsx) formats and advanced formulas.Easily count values across rows and columns using intuitive built-in functions like COUNTIF.Lightweight software that installs quickly and runs smoothly on all devices.Familiar user interface makes migration seamless with zero learning curve.

Frequently Asked Questions

Why does my custom column formula return an error?

Errors typically occur if there are pre-existing errors in your source columns. Power Query's Record.ToList function will fail if it encounters an error in any cell of that row. You must replace or correct errors in the source data before applying the counting formula.

Does this formula require me to type out every column name?

No. The main advantage of using Record.ToList(_) is that it dynamically captures all columns in the current row automatically. If your dataset changes and new columns are added, the formula will still evaluate the entire row without needing updates.

Can I count a different word instead of 'Completed'?

Yes, you can count any specific value by replacing 'Completed' in the formula with your desired text. Make sure the text is wrapped in double quotation marks, for example, 'In Progress' or 'Pending'.

Is Power Query case-sensitive when counting text?

Yes, Power Query (the M formula language) is strictly case-sensitive. The formula will only count exact matches. For instance, 'Completed' will be counted, but 'completed' or 'COMPLETED' will be ignored. To avoid this, you can transform the entire table to a uniform case before applying the formula.

Excel & Data Problems