Forum Discussion
Creating a table from survey responses with number values instead of text responses
Hey guys,
I am working with survey data which put simply has the questions as columns and the respondents as rows. So each column has values in it either like agree/ disagree, satisfied/ dissatisfied, very important/ not at all important.
For part of my report I use the table like this, but I also need another table laid out exactly the same but instead of those text responses I want each column to be filled with numbers from 1-5 (i.e. strongly agree = 5). The reason is that I want to do some weighted averages of different questions and figure this will be the easiest way to do this.
So does anyone know how I can basically duplicate the text response table and convert all the text responses to numbers based on another lookup table?
Hoping I've explained this clearly, and I really appreciate any information anyone can provide.
P.S. Possibly relevant, I also have the same data in a table that has been unpivoted - so each question is a new row with the corresponding response, and next to that I have the numeric value. I just have no flipping idea how I would do a weighted average with the data in this format (DAX is very daunting for someone that has been using Excel for 10 years).
Thanks!
5 Replies
v-ljerr-msftMicrosoft Employee
Hi sallen,
Could you post all your table structures with some sample data, and your expected result, so that we can better assist on this issue? It's better to share a sample pbix file. You can upload it to OneDrive or Dropbox and post the link here. Do mask sensitive data before uploading. :smileyhappy:
Regards
I have the same problem, I have many question with answeres that can be Satisfied, Dissatisfied, Very dissatisfied...
I need to count the amount of each category (Satisfaied = 80, Very Satisfied = 10 ..) and also convert them into number from 1 to 5 depending on the answered ( Satisfied =4, Very Satisfied =5...)
You can create add a new table in your model that maps the string values to numbers and then in Power Query merge your table with the created table.
Hi there - I'm stuck on this exact scenario in Power BI - I want to convert my survey answers "1-Not Satisfied, 2- Neutral, 3-Satisfied" etc. to numbers so I can get the weighted average. The tip included states to create a power query in Excel - but how can I do this in Power BI? I have loaded a table to map the answers to numbers, but how can you map that for each question (25 total)?
