On January 27, 2021, Google is changing the required permissions for attaching IAM roles to service accounts. If you are using IAM roles for your Google service accounts, please see Changes to User Management.
This section provides simple examples for how to use the COUNTA
and COUNTDISTINCT
functions. These functions include the following:
COUNTA
- Count the number of values within a group that meet a specific condition. See COUNTA Function.COUNTDISTINCT
- Count the number of non-null values within a group that meet a specific condition. See COUNTDISTINCT Function.
Source:
In the following example, the seventh row is an empty string, and the eighth row is a null value.
rowId | Val |
---|---|
r001 | val1 |
r002 | val1 |
r003 | val1 |
r004 | val2 |
r005 | val2 |
r006 | val3 |
r007 | (empty) |
r008 | (null) |
Transformation:
Apply a COUNTA
function on the source column:
Transformation Name | New formula |
---|---|
Parameter: Formula type | Single row formula |
Parameter: Formula | COUNTA(Val) |
Parameter: New column name | 'fctnCounta' |
Apply a COUNTDISTINCT
function on the source:
Transformation Name | New formula |
---|---|
Parameter: Formula type | Single row formula |
Parameter: Formula | COUNTDISTINCT(Val) |
Parameter: New column name | 'fctnCountdistinct' |
Results:
Below, both functions count the number of values in the column, with COUNTDISTINCT
counting distinct values only. The empty value for r007
is counted by both functions.
rowId | Val | fctnCountdistinct | fctnCounta |
---|---|---|---|
r001 | val1 | 4 | 7 |
r002 | val1 | 4 | 7 |
r003 | val1 | 4 | 7 |
r004 | val2 | 4 | 7 |
r005 | val2 | 4 | 7 |
r006 | val3 | 4 | 7 |
r007 | (empty) | 4 | 7 |
r008 | (null) | 4 | 7 |
This page has no comments.