Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago
Solved

Multiple codes in one cell - options for analysis

Dear Community, 

 

I am trying to analyze a survey with PowerBI that has a number of Multiple Choice questions, whereas each response option is associated with an ID (1,2,3,...; 1=bus, 2=car etc.). My database is built in a way, that all IDs selected for a single questionnaire a stored in one single cell in the format: 

 

                                               Column

Questionnaire ID  1                1,2,4,8

Questionnaire ID  1                2,5,7

...

 

I would like to create a report which counts the different IDs, i.e. how many times is "1", "2", "3" (...) mentioned and visualize that in a simple bar diagramm. However, all option I tried so far did not really work as I had hoped. 

 

Is there any simple solutions to this? I would be very grateful for any help. 

 

Thanks a lot!

Simon 

 

Need Help

3 Replies

  • v-chuncz-msft's avatar
    v-chuncz-msft
    Community Support
    7 years ago

    Anonymous,

     

    You may select Split Column By Delimiter > Advanced options > Split into Rows in Query Editor.

    • Anonymous's avatar
      Anonymous
      Not applicable
      7 years ago

      Thanks!

      I tried that but the effect is that the number of lines in my table increases, which in turn increases the count for any data analyses (i.e. I have ca. 500 surveys, but due to the multiplication of questionnaire IDs, PowerBI implies that I have much more than these 500 surveys. And this might even skew the analyses I am running, if I am not mistaken. Any solution to this as well?

       

      Best wishes

      Simon