Help counting parts of individual or unique strings in a column

[alex_311]

Hello,

I'm trying to analyze the results of an online survey that I have in .csv format. Each row is an individual user's response. Each column is a specific question. Some of the questions allowed the respondants to pick more than one answer. These are represented within the specific cell with each answer delimited by a "|".  Below I have a section of one of the columns:

Basically I want count indidiually each isntance of "SAS", "R", "SPSS" etc. How do I count each of the individual answers in the column so I can get a total for each answer?

Thought I could do it by splitting the columns but couldn't figure out how to get the count to work over a field. Also tried using this kind of DAX code:

CountWhyDataViz = COUNTROWS(FILTER('ProgrammingSurveyResults-2-14-2019', 'ProgrammingSurveyResults-2-14-2019'[What do you use to edit code? (select all that apply)] = "SAS"))

But that only works to count SAS when it's the first string in the cell. It won't count any string that is contained with in the cell.

I have five multiple answer columns like this.

I'm sure it's simple and I'm being thick. I appreciate any help.

Thanks!

[parry2k]

@alex_311 in power query (query editor), you can split the column by pipe (|) and then unpivot your answers, once it is done, it will be much easier to analyze it.

If you need further help, just share sample data in excel using one drive/google drive

[alex_311]

Thanks for the response!

Unfortunately it doesn't work because the split columns dont all hold the same value. So for example, if  respondant1 skipped the first possibile response but chose the second then the first column for them, after the split has the second possible response in it. If respondant2 did choose the first possible response then, after the split, that first response is in the first new column. Since these have different values I can't un-pivot the tables and then rename the columns tot he same values as the responses.

Here's before and after the split on the column in question:

BeforeColumnSplit.xlsx

AfterColumnSplit.xlsx

I hope that makes sense.

[parry2k]

@alex_311 looking at it now.

[parry2k]

@alex_311 here you go, solution attached.

[alex_311]

That's exactly what I was looking for, and it works if it's just the one column, though I see it doesn't make as much sense out of context though. There are a bunch of columns like this, and other data points too. So splitting into rows duplicates the rows of the other columns. I've attached a more complete set with identification removed.

MoreCompleteBeforeSplit.xlsx

[parry2k · Accepted solution]

@alex_311 attached with multi question, i just gave you example and you can change the way you want. Basically anything can be achieved.

[alex_311]

This is great! Unfortunately, I don't understand the steps you took to get here. It doesn't look like you actually manipulated the data itself less and relied on the count, but I'm not sure of that either. Can you lay out the steps taken to achieve this result?

[parry2k]

@alex_311 everything is in power query, you can go thru each step and in power query and check what is done.

[alex_311]

Oh! Now I see. You unpivoted the columns first and then split them by delimiter. I was looking at the wrong table - one I'd been messing around with. I'm going to try a couple of things but I think this is solved. If so, I'll mark your answer as the solution.

Thanks!

[parry2k]

@alex_311 sounds good. glad you are finding it useful

[alex_311]

Right. I'm looking at power query and I don't understand how what you've done gives the results. It doesn't look like it should since you only split one column by delimiter but there are 5. Also, making the split messes up the results of the last column "Do you have any exposure with or exposure to version control". Sorry if I'm being thick, I'm just nable to make the connection from what was done t what's displayed. I'm new to PowerBI.