Survey Scoring Table

[Anonymous]

Hi all,

I have a survey from survey monkey that I have loaded into Power Bi. Its a column (the question) with the response below it as the value.

In addition to this, I created a table that outlines how many points each value is worth below:

What I'm trying to do is either create a new table that replaces the responses with point values (it would look like the top picture except with points as values) 

OR

Come up with a way to replaces the words within the table with point values.

Can anyone share with me how I would best go about this?

Thank you in advance!

[snokku]

Hi @Anonymous, Usually for Survey questions, it is easier if you pivot your table.

1.) Pivot your Question table as below.

ID	Question	Answer
1	Question1	Answer1
2	Question2	Answer2
3	Question3	Answer3

2.)Points table,

Answer	Points
Answer1	3
Answer2	1
Answer3	0

Create 1-1 or 1-* relationship between Points[Answer] and Question[Answer] columns,

And then create a new table visual, with columns Question[Question] and Points[Points].

PowerBI Pivot

Hope this helps.

Thanks,

@snokku 

[Anonymous]

Thanks @snokku. I would do that but how do I not pivot the ID portion of the row. The respondant's ID is on the same row as the responses. Is there a way to pivot just the responses while keeping ID as a column?\

Thanks in advance.

[snokku]

Hi @Anonymous,

Just select the columns aside from ID column when you pivot/unpivot.

Thanks,

@snokku 

[amitchandak]

@Anonymous , You can replace option in Power Query editor /Data Transformation

https://docs.microsoft.com/en-us/power-bi/connect-data/desktop-shape-and-combine-data

Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.

[Anonymous]

@amitchandak My forum status won't let me share pbix documents for some reason. But I'll create tables in here if that helps.

How often are employees equipped?	Are observational routines utilized?	Do these take into consideration safety guidelines?
Sometimes	Yes	No

Sometimes	2
Yes	1
No	0

I want these to turn into this:

How often are employees equipped?	Are observational routines utilized?	Do these take into consideration safety guidelines?
2	1	0

Does this help?