Power BI pulling multiple versions of same answer from Forms

[BrandonP2121]

Hello,

I will explain this as best I can. I created a Form and have that data pulling into Power BI. Question 1 on my Form is a regular single answer multiple choice question, and Question 2 is a multi answer question. When someone picks multiple options for Question 2, it pulls the answer from Question 1 but adds a semicolon to it, and makes it its own category on a bar chart.

For example, Question 1 asks "Where did you have dinner last night?" and Question 2 has a bunch of options of what you could've had for dinner. If you answer Question 1 with "Papa Johns" and you answer Question 2 with "Pizza," everything works like I want it to. But if you answer Papa Johns for Question 1 and for Question 2 you answer "Pizza, Breadsticks" then in my graph I have Papa Johns, and Papa Johns;. I am not sure why it is adding a semicolon, and I want it to show on my graph as just 2 answers of Papa Johns.

Is there a way to get around this?

[edhans · Accepted solution]

Yes. It would involve splitting and parsing data in Power Query, but to assist, I'd need to see some real data to ensure I am understanding correctly and can post back a solution. A "good" row and a "bad" row with this duplication would be helpful so I can know what you want vs what you are currently getting.

And what is the data source? You said form... is this Microsoft Forms using Excel, or something else?