How can I combine two data columns being used as separate filter controls into one single filter control without losing any values or data?

Question

Question: How can I combine two data columns being used as separate filter controls into one single filter control without losing any values in Google Data Studio?

• I have attempted to use a calculated field of "Coalesce([column1],[column 2])". This works mostly, however, sometimes values in the two columns are different and I am losing a few values that need to be included in the list.
• I am pulling data from BigQuery using a custom query. The two columns have different names, but have the same data type (text). Both columns are a part of the same table/data set
• I cannot list the specific values in this post because of data sensitivity, however I've included an example below.
• Sometimes when the columns are viewed side by side, one column contains a null value, OR both values could be the same, OR both values could be different

Example

Column A
ABCDE
FGHIJ
KLMNO
GGGG

Column B
PQRS
TUVW
XYZA
GGGG

Side by Side View - This is what the data looks like in tabular form:

Column A	Column B
null	ABCDE
PQRS	FGHIJ
TUVW	null
XYZA	KLMNO
GGGG	GGGG

The coalesce function does not work because I am losing values where the two columns have different values - Coalesced Filter List will only include:

• ABCDE
• PQRS
• TUVW
• XYZA
• GGGG

Missing Values:

• FGHIJ
• KLMNO

Filter List Should Be:

• ABCDE
• PQRS
• TUVW
• XYZA
• GGGG
• FGHIJ
• KLMNO

Question Reiterated: I want to combine these two columns as one list to be used in a single filter dropdown in Google Data Studio without losing values.

Sample Data:

• Data Set (Google Sheets)
• Google Data Studio report

Answer 1

The solution below is similar to the suggestion in the question How can I make single control field to control multiple columns in Google Data Studio?, where the CONCAT function is used to join multiple fields (in this question, two fields titled Column A and Column B, respectively) by creating a single CSV (comma-separated values) field so that the CSV Filter Control Community Visualisation can be used to display and filter the required value(s); the difference being the possibility of NULL values in the fields:

Setup Tab

• Column to filter on:

  CASE
    WHEN Column A IS NULL AND Column B IS NOT NULL THEN Column B
    WHEN Column A IS NOT NULL AND Column B IS NULL THEN Column A
    WHEN Column A IS NOT NULL AND Column B IS NOT NULL THEN CONCAT(Column A, ",", Column B)
    ELSE NULL
  END
  

• Cross-filtering: Select (☑) (this makes sure that filtering values will apply to all charts)

Style Tab

• OR instead of AND behaviour: Select (☑) (data for multiple values in Column A or Column B can be viewed, when clicked)

Note that the calculated field above can be shortened, for example, the first WHEN:

WHEN Column A IS NULL AND Column B IS NOT NULL THEN Column B

could be:

WHEN Column A IS NULL THEN Column B

However, the purpose of the CASE is to ensure that a single field (no support fields required) is created that also serves as self-documenting code, where each WHEN clause is clearly understood by the editor or viewer, as all three WHEN clauses are consistent, where the exact criteria is specified for Column A and Column B in all three instances.

Subsequently, the ELSE NULL in this scenario, serves the purpose of:

WHEN Column A IS NULL AND Column B IS NULL THEN NULL

Publicly editable Google Data Studio report (embedded Google Sheets data source) and a GIF to elaborate:

Answer 2

I am pulling data from BigQuery using a custom query

Use below as a custom query

select distinct col
from your_table, unnest([ColumnA, ColumnB]) col
where not col is null             

if applied to sample data in your question - output is