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.