Search code examples
countpivot-tabledistinctdistinct-valuesexcel-2019

Distinct count in pivot adds 'empty' to pivot

I was happy to find out there is an option for a distinct count in pivottables. I used it, but found out something happened which I cannot explain:

When I use the normal count command, I get a nice, decent table containing all the values in the (in this case) column. I checked the cells, I have filled all the empty cells with a value:

count image

but when I switch to unique count, suddenly the table has an 'empty' between the values. But only 1:

uniqe count table

I checked the data, there is no empty between the values. When I select the column, use 'go to' > 'special' > 'empty cells' I get this message: no cells found

'No cells found'.

Why does this happen when using the unique count option?

I check the end of the data range (select the last filled row: ctrl + arrow down) in my file: end data table

this is the file with the column headers in row 1.

When I compare this to Powerpivot (select the last row, ctrl + arrow down), I see the Powerpivot range ends in row 369692 while this should be 369691 (the column headers are not in row 1 anymore): enddata powerpivot

The only thing I can come up with now is that I see powerpivot works with blocks of 4 rows apparently, where the last row in the last block is empty:

powerpivot rows

When I filter the specific column in Powerpivot on 'Empty', I get this: powerpivot empty filter

The row number is strange because row 1 is filled normally: enter image description here

But still, how does Powerpivot find an empty row in the data range when there is no empty row?

Solution

  • Possible causes with solutions

    • hidden characters or spaces in the data that are causing the (blank) value to appear. using the TRIM function will remove them

    • Issue with the data source or the connection to the data source. You can try refreshing the data source and see if that resolves the issue.

    • placeholder row in your data source could be causing the (blank) value to appear when using the Distinct Count function in a pivot table. If there is a placeholder row in your data source that contains empty cells or cells with hidden characters or spaces, it could be counted as a unique value by the Distinct Count function.