How to pivot a table in excel from long to wide

Question

I have the following table that I'd like to pivot from a "long" form to a wide form. Each patient can have several procedures (procedure_code). In my existing table, a patient with multiple procedures has multiple records in the table

patient_no,FirstName,procedure_code
1,Hana,PROC_X
2,Robert,PROC_X
2,Robert,PROC_Y
3,Stepan,PROC_Z
4,Ahmed,PROC_Z

I'd like to have one line per patient and have the procedures flatten in columns indicating the procedure order like so:

patient_no,FirstName,PROC_NUMBER_1, PROC_NUMBER_2, PROC_NUMBER_3, PROC_NUMBER_4
1,Hana,PROC_X,,,
2,Robert,PROC_X,PROC_Y,,
3,Stepan,PROC_Z,,,
4,Ahmed,PROC_Z,,,

How could I do that using the Power Query?

Answer 1

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"patient_no", Int64.Type}, {"FirstName", type text}, {"procedure_code", type text}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"patient_no", "FirstName"}, {{"All", each _, type table [patient_no=nullable number, FirstName=nullable text, procedure_code=nullable text]}}),
    #"Added Custom" = Table.AddColumn(#"Grouped Rows", "Custom", each Text.Combine([All][procedure_code], ",")),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Added Custom", "Custom", Splitter.SplitTextByDelimiter(",", QuoteStyle.Csv), {"Custom.1", "Custom.2"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Custom.1", type text}, {"Custom.2", type text}}),
    #"Removed Columns" = Table.RemoveColumns(#"Changed Type1",{"All"})
in
    #"Removed Columns"

Group

Add custom column

Split by delimiter