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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

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
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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