CODEWITHSUNDEEP
looker-studio
lusk
lusk

Reputation: 562

Comma-separated values as filter control in Google Data Studio

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:

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

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

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

Upvotes: 4

Views: 5164

Answers (1)

Nimantha
Nimantha

Reputation: 6471

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:

6]

Upvotes: 3

Related Questions