Looking for some help with an issue that I have. I've tried several things but not having much luck. Looking for advice on how to best approach this.

I have a data source that looks like the below. It currently is an Excel worksheet, but I'm working on getting this data into a database but it will still look the same.

Support ID

Products

Quotes

Locations

SID001

Product1; Product2

1

4

SID002

Product2; Product4

2

2

SID003

Product1

1

2

SID004

Product3; Product4

3

6

SID005

Product2

1

2

SID006

Product3

1

6

What is the best way to split the products column? I know that it would be preferable to have "tall" data as opposed to "wide" data, but if I create multiple rows to break out the products then the Quotes and Locations column gets counted multiple times, which will inflate those numbers. If I split the column, how should I combine them to create one Product column to use for Product trends?

Thanks so much! This acts similarly to the Split function on the Data Source tab. How can I then use the multiple columns to have one overall column where each product counts as 1? You can see what I'm trying to accomplish in the attached workbook (I've re-uploaded another version) on the Products Tall worksheet. If I split each column, I end up with the Products Wide when I re-combine them or keep it the same.

Robert's suggestion will create new columns but there is no good way to unify those columns for analysis. You're going to need to re-shape your data to be tall, which makes product-level analysis easy, and then use an LOD expression to avoid double-counting total quotes and locations. TWBX attached.