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

Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now

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.

Join us as experts from around the world come together to shape the future of data and AI!
At the Microsoft Analytics Community Conference, global leaders and influential voices are stepping up to share their knowledge and help you master the latest in Microsoft Fabric, Copilot, and Purview.
️ November 12th-14th, 2024
 Online Event
Register Here
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
October NL Carousel

Fabric Community Update - October 2024

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