Complex pivot of questions with ID's

[CHealy]

I have on column that has some duplicate ID's that I am trying to pivot from long to wide 

ID's	Questions
A	q1
A	q2
B	q3
B	q4
B	q5
C	q6
D	q7
D	q8
E	q9

 taking this data set to make it look like this 

ID's	Q variant 1	Q variant 2	Q variant 3
A	q1	q2	null
B	q3	q4	q5
C	q6	null	null
D	q7	q8	null
E	q9	null	null

I do not know how to do it but I was thinking of making a reference column corresponding to which column the question needed to be in then pivot with the reference column being the header 

ie 

ID	Question	reference
A	q1	1
A	q2	2
B	q3	1
B	q4	2
B	q5	3

any help or suggestions would be appriciated 

Thank you

[AmiraBedh · Accepted solution]

let
    
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WclTSUSo0VIrVgTKNwEwnENMYwTRBME3BTGcQ0wzMdAExzRFMCzDTFcS0VIqNBQA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"ID's" = _t, #"Questions " = _t]),
    
   
    RenamedColumns = Table.RenameColumns(Source, {{"Questions ", "Questions"}}),
    
    SortedTable = Table.Sort(RenamedColumns, {{"ID's", Order.Ascending}, {"Questions", Order.Ascending}}),
    
    GroupedTable = Table.Group(SortedTable, {"ID's"}, {{"AllData", each Table.AddIndexColumn(_, "Reference", 1, 1, Int64.Type)}}),
    
    ExpandedTable = Table.ExpandTableColumn(GroupedTable, "AllData", {"Questions", "Reference"}),
    
    ConvertedReference = Table.TransformColumnTypes(ExpandedTable, {{"Reference", type text}}),
    
    PivotedTable = Table.Pivot(ConvertedReference, List.Distinct(ConvertedReference[Reference]), "Reference", "Questions"),
    
    RenamedPivotedColumns = Table.RenameColumns(PivotedTable, {{"1", "Q variant 1"}, {"2", "Q variant 2"}, {"3", "Q variant 3"}})
    
in
    RenamedPivotedColumns