Pivot Table Issues

How to Fix Power Pivot Distinct Count Including Blank Values in Excel

Guest Writer Oct 10, 2026 871 views

Question details

Prevent Power Pivot's distinct count from including visually blank cells that contain hidden spaces or empty strings.

How to Fix Power Pivot Distinct Count Including Blank Values

Product: Microsoft Excel
Device & OS: not provided
Scenario: Creating a PivotTable using a Data Model and DAX distinct count, but the summary inaccurate because empty-looking cells are being counted as unique items.
Observed behavior: The distinct count measure includes cells that look blank because Power Pivot evaluates empty strings, spaces, or NullStrings as actual data values rather than true nulls.

Before you start

Ensure you have access to the original raw dataset and verify that your Excel version supports the Power Query and Power Pivot add-ins.

Solution 1Recommended

Clean Blank and Null Values Using Power Query

This is the most effective approach to permanently resolve hidden spaces and empty strings by converting them to true nulls before they enter the Data Model.

Power Query processes data before it reaches Power Pivot. By replacing empty strings and spaces with true null values here, the DAX distinct count function will automatically ignore them.

Open Power Query Editor

Go to the 'Data' tab on the Excel ribbon, select 'Get Data', and load your source table into the Power Query Editor.

Replace Empty Strings and Spaces

Select the affected column, right-click the header, and choose 'Replace Values'. Enter a space or leave the 'Value To Find' blank (for empty strings), and type 'null' (lowercase) in the 'Replace With' field.

Trim Whitespace

To ensure no hidden spaces remain, right-click the column header, navigate to 'Transform', and select 'Trim'.

Load to Data Model

Click 'Close & Load To...', select 'Only Create Connection', and check the box for 'Add this data to the Data Model'. Refresh your PivotTable to see the accurate distinct count.

Clean Blank and Null Values Using Power Query

True Nulls Verified: Once transformed into genuine null values, DAX functions like DISTINCTCOUNT will correctly exclude these rows from the final calculation.

Solution 2

Identify False Blanks Using ISBLANK in DAX

Use this diagnostic method to troubleshoot and confirm whether your empty-looking cells are actually being treated as blank by the Data Model.

Free Microsoft Office alternative

Handle Complex Data Easily with WPS Office

While Power Pivot and DAX are specific to Microsoft Excel, WPS Spreadsheet offers robust standard Pivot Table capabilities that can effortlessly manage everyday data analysis. Enjoy a lightweight, highly compatible alternative for your spreadsheet tasks without the heavy resource usage.

1. Install WPS Office: Download and install the free WPS Office suite on your device.
2. Open Your Dataset: Launch WPS Spreadsheet and seamlessly open your existing .xlsx workbooks.
3. Create a PivotTable: Go to the Insert tab, select PivotTable, and easily summarize your cleaned data.

Highly compatible with Microsoft Excel (.xlsx) formatsRobust built-in Pivot Table features for standard data summarizationLightweight application that runs smoothly on older devicesFamiliar user interface requiring zero learning curve

microsoft office alternative - wps office

Frequently Asked Questions

Why does DAX treat my empty cells as non-blank?

DAX differentiates between a true NULL value and an empty text string ("") or space. If your data source imported empty strings, DAX counts them as distinct text values, which inflates your distinct count results.

Can I filter out blanks directly in the Power Pivot distinct count measure?

Yes. Instead of modifying the source data, you can create a DAX measure using CALCULATE and DISTINCTCOUNT, combined with a FILTER function to exclude blank or empty string values, such as: CALCULATE(DISTINCTCOUNT(Table[Column]), FILTER(Table, Table[Column] <> "")).

Does the standard Excel Pivot Table have a distinct count feature?

Yes, standard Pivot Tables can calculate distinct counts, but only if you check the 'Add this data to the Data Model' box when creating the PivotTable. Once added, 'Distinct Count' becomes available in the Value Field Settings.

Excel & Data Problems