Creating a table from survey responses with number values instead of text responses

[sallen]

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!

[Anonymous]

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)?

[v-ljerr-msft]

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. 

Regards

[claudialan14]

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...) 

[thetom]

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.

See: https://support.office.com/en-us/article/merge-queries-power-query-fd157620-5470-4c0f-b132-7ca2616d1...

[thetom]

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.

See: https://support.office.com/en-us/article/merge-queries-power-query-fd157620-5470-4c0f-b132-7ca2616d1...