Have a field that contains a list of comma separated employee numbers, need to join

Looker Q&A Looker Studio
,
1

I have a single field/column in a spreadsheet data source of classes taken that contains a “list” of comma separated employee numbers that represent the attendees. I need to “join” this field to another table that contains employee numbers and has their names and other info. The end result is I need to be able to display a table that has a row for either each class showing the names of the attendees in a column, or each person and the classes they have taken. I can get the table to display, but any row in the class table that more than one employee number in the “list” of attendees comes up “Null” because it can’t sort out that there are multiple results in the single column.

How can I do this in Looker Studio?

2

You’ll need to restructure your data so that instead of Class | List of Numbers it has Class | Number, with a row for each person in the class

Instead of
Math | 1234, 4567, 6789
You need this data structure
Math | 1234
Math | 4567
Math | 6789

You essentially need to turn wide data into long data. There isn’t a way to do this in Looker Studio; you’ll need to do it in your source. If you are using Google Sheets you may find Ben Collins’ tutorial on how to “unpivot” data to be helpful.

You can then join the number/name data using a blend in Looker Studio, or VLOOKUP in Sheets, then use the merged Sheet source as your report source.

1 Like
3

That is what I was afraid someone was going to say, lol. Thank you!