Best way to categorize multiple columns with multiple values in each cell

[Anonymous]

Hello everyone!

 I need to create a dashboard out of a microsoft form that is already in use for a while. The problem that I am having is the extract has around 50 columns with around 10 columns have multiple answers within 1 cell ( it is a check box in the form with max of 10 answers).

ex.

ID	Categories
uniqueID1	cat1;cat2;cat3;cat4;cat5;cat6;cat7;cat;8;cat9;cat10
uniqueID2	cat1;cat2;cat3;cat8;cat10
uniqueID3	cat4;cat5

the example above happens to be on other columns as well. Counting the values in each cell will not help as I need it to create a visual out of the categories.

Do you have any best practice in this case or the only way is to split the values into columns?

Thank you!

[amitchandak · Accepted solution]

@Anonymous , the Ideal would be to split that into new rows. but with 10 such columns, it might explode in terms of rows.

and the other way is to have an independent table with all search values(cat1,cat2,cat3, etc) and use search to create measure

but if you select multiple values in such case it would be another challenge

[Anonymous]

Really difficult but I will try your suggestion. Thank you so much!