Alternative to split/unpivot?

[ViniciusLeyro]

So I have a table (googlesheets) from a research with multiple questions, and many questions have multiple answers separated by commas. It's quite extensive, there are like 20 questions and each one has at least 5 items to choose.

Something like: (not the real table)

Tom	Yellow, Blue, Red	Orange, Apple, Lemon	Morning, Evening	Football, Tennis
Jerry	Blue, Black	Orange, Lemon, Grapes	Morning, Afternoon	Basketball, Tennis
Adam	Yellow, Red, Black	Apple, Lemon, Peach	Afternoon, Evening	Football, Badminton
Steve	Red, Black	Lemon, Grapes	Evening, Night	Tennis, Badminton

Well I have to bring up an analysis of all the different items while still correlating them. I'm using the split into lines method and it works fine.

BUT then this table went from 156 answers (until now) to more than 10.000 lines of data.

And since it's a research that is still going on, answers are still coming and we expect something close to 1.000 answers. How big will this get?

So my question is: is there another method to this, so I don't end up with a table the size of the universe?

Or: is it even a problem to have a table this big? What kind of issues this may cause?

[MFelix]

Hi @ViniciusLeyro ,

In this case the best option is the one you are using since this allows the flexibility to make your anilisys. Power BI have no issues with the additional lines on your model, only question that can happen is the time to refresh but this depends on the source and your licensing.