Reporting on long text strings separated by a semi-colons

[eamonnj1982]

Hi,

I am trying to report on the results of a large survey where the users were allowed to select multiple reponses to each question. 

There was a separate database field created to record the responses to each question and if the user selected multiple responses then they are separated by a semi-colon in the field. 

For example (I have all the responses for Questions 1 stored like this):

Question1: Response 1;Response4;Response9

Question1: Response 2;Response8

Question1: Response 3;Response5;Response6

Question1: Response 4

Could anyone offer the best way to be able to report on each response?

Many thanks

E 

[ankitpatira · Accepted solution]

@eamonnj1982 I would suggest to split up responses column via power bi desktop query editor using semi-colon as delimiter. Then under Transform tab use Unpivot Column feature to convert collect all responses in one column against questions and then simply use Matrix visual to report on responses to each question.

[eamonnj1982]

Thanks for the suggestion Ankit.  I'll give this a go.