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:
but when I switch to
unique count, suddenly the table has an 'empty' between the values. But only 1:
'No cells found'.
Why does this happen when using the unique count option?
this is the file with the column headers in row 1.
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:
But still, how does Powerpivot find an empty row in the data range when there is no empty row?
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.