Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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:
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!
Solved! Go to 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.
Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!
Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo
If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤
Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.
@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
Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!
Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo
If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤
Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.
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:
I hope that makes sense.
@alex_311 looking at it now.
Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!
Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo
If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤
Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.
@alex_311 here you go, solution attached.
Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!
Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo
If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤
Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.
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.
@alex_311 attached with multi question, i just gave you example and you can change the way you want. Basically anything can be achieved.
Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!
Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo
If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤
Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.
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?
@alex_311 everything is in power query, you can go thru each step and in power query and check what is done.
Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!
Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo
If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤
Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.
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!
@alex_311 sounds good. glad you are finding it useful
Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!
Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo
If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤
Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
109 | |
98 | |
80 | |
64 | |
57 |
User | Count |
---|---|
145 | |
110 | |
91 | |
84 | |
66 |