I am looking for a way to convert a table (ideally, automatable on field update) in this format:
| Project ID | Impacted Team(s) |
|---|---|
| 101 | 1, 2, 3 |
| 102 | 2 |
| 103 | 1, 3 |
to this format:
| Project ID | Impacted Team |
|---|---|
| 101 | 1 |
| 101 | 2 |
| 101 | 3 |
| 102 | 2 |
| 103 | 1 |
| 103 | 3 |
so that I can filter on multi-select fields (comma delimited values) in BI tools such as Tableau. I’ve seen this format referred to as a “join lookup” table in a previously used application.
Tableau Desktop alone cannot do this via split & pivot as pivoting on calculated fields (split) is not supported. Tableau Prep, which can do this, does not support WDC connected data sources, such as Airtable requires.
Any suggestions?