How to transfrom questionaire responses into separate columns

[N-msft]

Hi I have a table that contains responses for their respective quesions on a scale of 1 to 5 as below :  

ID	Responses
1001	Q1-3 | Q2-5 | Q3-4 | Q4-2 | Q5-5
1002	Q1-3 | Q2-4 | Q3-4 | Q4-4 | Q5-3
1003	Q1-3 | Q2-2 | Q3-4 | Q4-2 | Q5-1
1004	Q1-3 | Q2-1 | Q3-4 | Q4-2 | Q5-5

How can I transform these responses in power query as :

ID	Q1	Q2	Q3	Q4	Q5
1001	3	5	4	2	5
1002	3	4	4	4	3
1003	3	2	4	2	1
1004	3	1	4	2	5

[N-msft]

Thank you so much.

[jdbuchanan71 · Accepted solution]

In Power Query

1. Split Responses by delimiter "|"

Select ID and Unpivot other columns

Remove the attribute column and trim the Value column then split the Value column by delimeter "-"

Select the Value.1 column and pitvot the column with Value.2 using no aggregation.

And you end up with this.

Here is the code from the advanced editor for all the steps.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjQwMFTSUQo01DVWqFEINNI1BVHGuiYgykTXCESZ6poqxeqA1RqhqDVBUWsCUWsMU2uMotYIm7mGMLUmKGoNcbghFgA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [ID = _t, Responses = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"ID", Int64.Type}, {"Responses", type text}}),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Changed Type", "Responses", Splitter.SplitTextByDelimiter("|", QuoteStyle.Csv), {"Responses.1", "Responses.2", "Responses.3", "Responses.4", "Responses.5"}),
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Split Column by Delimiter", {"ID"}, "Attribute", "Value"),
    #"Removed Columns" = Table.RemoveColumns(#"Unpivoted Other Columns",{"Attribute"}),
    #"Trimmed Text" = Table.TransformColumns(#"Removed Columns",{{"Value", Text.Trim, type text}}),
    #"Split Column by Delimiter1" = Table.SplitColumn(#"Trimmed Text", "Value", Splitter.SplitTextByDelimiter("-", QuoteStyle.Csv), {"Value.1", "Value.2"}),
    #"Pivoted Column" = Table.Pivot(#"Split Column by Delimiter1", List.Distinct(#"Split Column by Delimiter1"[Value.1]), "Value.1", "Value.2")
in
    #"Pivoted Column"