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?