newbie transformation question

[akoconnor]

Hello!  New to PQ and I may not have the proper vocabulary to ask this, possibly, simple question.

I have been given data that is iteritive in rows. I know I need to transform it but everything I try is not quite right. In the data below, for each question I would like to have a seperate column, but only one row for Sofia and her answers in the correct column. Thoughts?

What I have:

Name	Question	Answer
Sofia	Institution: - (MQ0194)	UAB
Sofia	Department/Division: - (MQ0195)	Neurobiology
Sofia	Registrant type: - (MQ0196)	Faculty: Instructor, Assistant Professor, Associate Professor, or Professor
Sofia	Please select your mentor from this list of Cancer Center Members: - (MQ0197)	Sofia  NO
Sofia	Will you be presenting a poster at this year's retreat? - (MQ0198)	No
Satoru	Institution: - (MQ0194)	UAB
Satoru	Department/Division: - (MQ0195)	Neurosurgery
Satoru	Registrant type: - (MQ0196)	Faculty: Instructor, Assistant Professor, Associate Professor, or Professor
Satoru	Please select your mentor from this list of Cancer Center Members: - (MQ0197)	Erwin  NO
Satoru	Will you be presenting a poster at this year's retreat? - (MQ0198)	No

What I want:

Name	Institution: - (MQ0194)	Department/Division: - (MQ0195)	Registrant type: - (MQ0196)	Please select your mentor from this list of Cancer Center Members: - (MQ0197)	Will you be presenting a poster at this year's retreat? - (MQ0198)
Sofia	UAB	Neurobiology	Faculty: Instructor, Assistant Professor, Associate Professor, or Professor	Sofia  NO	No
Satoru	UAB	Neurosurgery	Faculty: Instructor, Assistant Professor, Associate Professor, or Professor	Erwin NO	No

[ronrsnfld]

This is a simple Pivot on the Question column with No Aggregation

From the UI:

Code from Advanced Editor

let

//Change next line to reflect actual data source
    Source = Excel.CurrentWorkbook(){[Name="Table21"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Name", type text}, {"Question", type text}, {"Answer", type text}}),
    
    #"Pivoted Column" = Table.Pivot(#"Changed Type", List.Distinct(#"Changed Type"[Question]), "Question", "Answer")
in
    #"Pivoted Column"