Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

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.

Reply
Anonymous
Not applicable

Survey Scoring Table

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.

 

Annotation 2020-06-12 105503.png

 

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

 

Annotation 2020-06-12 105527.png

 

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!

 

5 REPLIES 5
snokku
Helper I
Helper I

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

1.) Pivot your Question table as below.

IDQuestionAnswer
1Question1Answer1
2Question2Answer2
3Question3Answer3

 

2.)Points table,

AnswerPoints
Answer13
Answer21
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
Not applicable

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.

Hi @Anonymous,

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

 

Thanks,

@snokku 

amitchandak
Super User
Super User

@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
Not applicable

@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?
SometimesYesNo

 

Sometimes2
Yes1
No0

 

I want these to turn into this:

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

 

Does this help?

Helpful resources

Announcements
Microsoft Fabric Learn Together

Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

April Fabric Community Update

Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.