Splitting data in PowerBI

[BrandonP2121]

Hello,

So i created a Form that has 2 questions that allow multiple answers. What I am looking to do is to when someone marks multiple answers down, is to have those answers be counted separately. For example, if someone marks down A and B on that question, currently it shows up as A,B when I try to graph it. What I want to do is to have A and B be counted separately so when I graph it I have a total of all options instead of every possible combination of answers.

What I have done is edited a Query where I split the data into columns by delimiter, but doing so gives me mulitple different headers. Is there a way to split the data, and then combine the headers back into one? I tried re-grouping the data but that gives me column with just 1s in it.

Thank you!

[OwenAuger · Accepted solution]

Hello @BrandonP2121 

In this case you should split the column into rows rather than columns. Luckily that option is built into the interface 🙂

In the Power Query editor, select the column, then go to Transform => Split Column => Split Column by Delimiter.

Then make selections as below, with Split into Rows selected under Advanced options:

Does that work in your situation?

Regards,

Owen

[BrandonP2121]

Yes that is what I was looking for, thank you!

I do have another question though, is there a way to link this query or use an equation to my data, so that whenever A,B shows up in like a graph for instance it would break A,B apart into A and B so that my graph would have a cummulative total of all As and Bs instead of combinations of it? Please let me know if this doesn't make sense, I'm new to Power BI and trying to convey what I am trying to do.

[OwenAuger]

That's good news!

For your second question, I'm not quite sure what you mean - could you clarify with a sketch or example of what you are looking for?

[Anonymous]

HI @BrandonP2121,

Maybe you can try to enable 'unpivot column' on these columns. It will convert your fields to attribute and value then you can simply calculate with each item:

Unpivot columns (Power Query) 

Regards,

Xiaoxin Sheng