How to get Looker to accurately parse comma separated values

[DaveM1]

I am totally stumped.

I am using a Sheet as my data source for a chart in Looker.

This Sheet is receiving Form responses, some of which include “Select any/all” checkbox responses. 

The result is I might get rows in my sheet that contain multiple responses in one cell, like “chocolate, strawberry”. 

The problem is that Looker does not understand the Form choices, it only reads the source data, so I might end up with a table chart in Looker that does not aggregate data correctly:

Chocolate - 1

Strawberry - 1

Chocolate, Strawberry - 1

When it should look like this:

Chocolate - 2

Strawberry - 2

I CAN use regexp and create a table chart that captures what appears ahead of the first comma, another chart that captures what appears after the second comma, etc but that is not scalable and ends up with me dealing with null values in each chart when it finds blanks in the comma separated lists. I end up with this:

Chart 1:

Chocolate - 2

Strawberry - 1

Chart 2:

null - 1

Strawberry - 1

I would have assumed that Looker Studio Pro would know how to account for comma separated values but it does not. How can I teach it to do what I need it to do? 

[Guinho_Stiehler]

I kinda need the same thing to work on my Forms database. Did it get any update?

[DaveM1]

No, no updates yet. If you find anything let me know! 

[adegboyegaFAU]

I was able to implement this using BigQuery (BQ) as the data source but I believe the same concept applies. Steps below
1. Create a unique ID for every observation in the source data
2. In Looker, connect the data source using a custom query which splits the comma separated values (CSV) so that you have 1 CSV per row (looks like this in BQ:
SELECT * EXCEPT(CSV_column) FROM TABLE, UNNEST(SPLIT(CSV_column, ', ') AS CSV_column_split
3. Create the table using CSV_column_split as dimension and ID as metric (count distinct)

So I think what you'd do in sheets since you can't use a custom query is find a way to split the comma separated values after creating the ID

Hope this helps.

[amnonkhen]

This shows how to do it with a BigQuery data source. Has any body done it using a Sheet data source?

[Guinho_Stiehler]

this remains a great mystery, my man.

I've seen people using the CSV Filter Control, from Community Visualizations. It does help to count the comma separated values ​​individually, but the charts will still show grouped values.

[Data_Survey_etf]

I found this article titled How to split comma-separated values to single values with Looker Studio but I'm still figuring it out. Haven't gotten it to work yet.