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:
but when I switch to unique count, suddenly the table has an 'empty' between the values. But only 1:
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'.
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:
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):
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:
When I filter the specific column in Powerpivot on 'Empty', I get this:
The row number is strange because row 1 is filled normally:
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 theTRIMfunction will remove themIssue 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 rowin your data source could be causing the(blank)value to appear when using theDistinct Count functionin apivot table.If there is aplaceholderrow in your data source that contains empty cells or cells with hidden characters or spaces, it could be counted as aunique valueby theDistinctCount function.
- Select counts from one table based in the results from another table
- Avoiding overlap in frequency and document frequency count in Quanteda
- Add column count of occurrences
- Get all data COUNT fastest from large table based on condition
- Output a list from a sentence and count characters for each word
- PHP: Count a stdClass object
- Query data and how many rows have that date in mysql
- Count "truthy" values in a 2d array
- Count the number of objects in an array of object with a specified property value
- Count rows of a 2d array which contain a qualifying column value
- Determine if two arrays have at least 3 shared values
- Count the occurrence of one vector's values in another vector
- Counting the number of specific occurrences in a java String
- Expand value_counts() view?
- Looping over Pandas columns while counting rows that contain two specified values
- Count elements in a row and create column counter in pandas
- How to use Count in a Pivot clause
- creating a chart using a count query on repository in symfony
- Why is mongodb collection count deprecated?
- Mysql count vs mysql SELECT, which one is faster?
- How to efficiently count the number of keys/properties of an object in JavaScript
- Pivot and count grouped values of a 2d array to create a new 2d array with zero-defaulted columns
- How can I find the number of unique characters in a string?
- How to apply chi squared test on a series of count tables?
- Count multiple letters in string Python
- Access is multiplying my query count by 38.... why?
- COUNT where SUM condition using DQL
- Return total number of rows found while paging in JAX-RS resource
- Counting all "-1" and -1 in a dataframe for a list of certain columns
- Equivalent of FOUND_ROWS() function in Postgresql