Comma-separated values as filter control in Google Data Studio

Question

I have a sheet that has a text field that contains strings of comma-separated values.

I'd like to create a filter control that is smart enough to take those comma-separated values and parse them as individual strings.

For example, I have a field called "Colors" and three rows with the following values:

1. Black
2. Yellow, Blue
3. Blue

Currently, a filter created from this field contains the following values:

• Black
• Yellow, Blue
• Blue

Ideally the filter would contain (and filter appropriately) the following values:

• Black
• Yellow
• Blue

And selecting "Blue" would return rows #2 and #3.

This seems like rather basic functionality—am I missing a way to do this?

Answer 1

A Long Data Structure is preferred over a Wide Data Structure.

One way this can be achieved in Google Sheets is to first JOIN the comma separated values, then SPLIT them into individual cells and finally, to TRANSPOSE them so that they are all stacked in a single column (where Sheet1 is the input sheet and A:A the range):

=TRANSPOSE(
{SPLIT(
{JOIN(", ",Sheet1!A:A)},
", ", FALSE, TRUE)})

Google Data Studio Report to demonstrate, as well as a GIF showing the process: