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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
ksab23
Helper I
Helper I

Transpose

Hi All, 

I would need some help with transposing data and for the life of me, I cannot figure it out. The dataset sample is attached. I need to transpose questions of the survey, answers, and scores (optional) into columns -> question text 'columns'+question answers 'rows' (there is also a score column but that one can be discarded).

Course TitleCourse IDQuestion TextQuestion ResponsePerson Full NamePerson E-mailEvaluation Submitted OnScore
  I am likely to change my behavior following this training.Agree  2023-04-274
  The design and duration was appropriate for the topic.Strongly Agree  2023-05-095
  The design and duration was appropriate for the topic.Strongly Agree  2023-03-095
  I would recommend this course for someone in my role.Strongly Agree  2023-05-095
  I am likely to change my behavior following this training.Neither agree nor disagree  2023-03-163
  I am likely to change my behavior following this training.Strongly Agree  2023-03-105
  The design and duration was appropriate for the topic.Neither agree nor disagree  2023-03-283
  Please explain your ratings and make suggestions for future training.N/A  2023-03-30 
  The design and duration was appropriate for the topic.   2023-04-12 
  I would recommend this course for someone in my role.Neither agree nor disagree  2023-03-283
  The design and duration was appropriate for the topic.Agree  2023-03-164
  Please explain your ratings and make suggestions for future training.None  2023-05-04 
  I am likely to change my behavior following this training.Agree  2023-05-124
  The design and duration was appropriate for the topic.Strongly Agree  2023-03-095
  The design and duration was appropriate for the topic.Strongly Agree  2023-03-305
  The design and duration was appropriate for the topic.Agree  2023-05-124
  I am likely to change my behavior following this training.Strongly Agree  2023-03-305
  I am likely to change my behavior following this training.Neither agree nor disagree  2023-03-283
  Please explain your ratings and make suggestions for future training.N/A  2023-03-09 


I deleted sensitive information from the dataset, however as the data is now, for each answer you have the course user took and score per question (not per course+questions, which is as stupid as it gets, as I cannot even get an avg score per course this way).

Ideal outcome (plus the columns I omitted, email, course name, etc.):

UserCourseQuestion1Question2Question3
XY1answeransweranswer
XY2answeransweranswer
X1Y1answeransweranswer



Thanks a lot!

1 ACCEPTED SOLUTION

@ksab23 

select quesion text column and click pivot column

choose queston column and  don't aggregate

 

1.PNG





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




View solution in original post

5 REPLIES 5
ryan_mayu
Super User
Super User

@ksab23 

pls see the attachment below.





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




exactly what I need, how did you do it please?

@ksab23 

select quesion text column and click pivot column

choose queston column and  don't aggregate

 

1.PNG





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Datagulf
Responsive Resident
Responsive Resident

I dont understand your expected output related to your question but, you should maybe add Pivots/ Unpivots  then transpose

Hi, my expected outcome is this (below) and I cannot make it work really with pivot/unpivot and transpose. No matter what combination I try, it always gives me a messed-up result. So I know what to use but not exactly how if that makes any sense

 

nameemailcourse TitleCourse IDdateI am likely to change my behavior following this training.The design and duration was appropriate for the topic.I would recommend this course for someone in my role.
     AgreeAgreeStrongly Agree
     Strongly AgreeStrongly AgreeStrongly Agree

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

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

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

Top Solution Authors