Survey Question & Answer from rows to column and rows

[Jdiep]

Hi all,

I am stuggling to figure out how to get my survey answers as preferred. I have two columns: Column1 is the question and Column2 is the answer. 

I want a table where the question is the columns and the answers are on the rows. So from example 1 to example 2. The steps have to work continiously since there will be more answers to the survey every day. The answer to Q1 is the identifier to make a relationship with another table.

Would love to hear your input/solution to this. If you need more info let me know, thanks in advance.

Regards,

Jur

Example1

Question;Answer
Q1;A1
Q2;A2
Q3;A3
Q4;A4
Q5;A5
Q6;A6
Q1;A7
Q2;A8
Q3;A9
Q4;A10
Q5;A11
Q6;A12
Q1;A13
Q2;A14
Q3;A15
Q4;A16
Q5;A17
Q6;A18

Example 2

Q1;Q2;Q3;Q4;Q5;Q6
A1;A2;A3;A4;A5;A6
A7;A8;A9;A10;A11;A12
A13;A14;A15;A16;A17;A18

[Vijay_A_Verma · Accepted solution]

Open a blank query - Home - Advanced Editor - Remove everything from there and paste the below code to test (later on when you use the query on your dataset, you will have to change the source appropriately)

Solution using Transpose

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("Tc6hEcAwDATBXoQN8pIlJzAlBHvcfxu2IvLs0M7NKR+kyQtZ7bRma7VlW3XP7tWe7dWRHdW/M8i5yXnIwUUQQBKUKBhZ6ITBWQvWBmtnYG0=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t, Column2 = _t]),
    #"Grouped Rows" = Table.Group(Source, {"Column1"}, {{"Temp", each Text.Combine([Column2],";"), type nullable text}}),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Grouped Rows", "Temp", Splitter.SplitTextByDelimiter(";", QuoteStyle.Csv), {"Temp.1", "Temp.2", "Temp.3"}),
    #"Transposed Table" = Table.Transpose(#"Split Column by Delimiter"),
    #"Promoted Headers" = Table.PromoteHeaders(#"Transposed Table", [PromoteAllScalars=true])
in
    #"Promoted Headers"

Solution using Pivots

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("Tc6hEcAwDATBXoQN8pIlJzAlBHvcfxu2IvLs0M7NKR+kyQtZ7bRma7VlW3XP7tWe7dWRHdW/M8i5yXnIwUUQQBKUKBhZ6ITBWQvWBmtnYG0=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t, Column2 = _t]),
    #"Added Index" = Table.AddIndexColumn(Source, "Index", 1, 1, Int64.Type),
    #"Inserted Division" = Table.AddColumn(#"Added Index", "Division", each [Index] / 6, type number),
    #"Inserted Round Up" = Table.AddColumn(#"Inserted Division", "Round Up", each Number.RoundUp([Division]), Int64.Type),
    #"Removed Columns" = Table.RemoveColumns(#"Inserted Round Up",{"Index", "Division"}),
    #"Pivoted Column" = Table.Pivot(#"Removed Columns", List.Distinct(#"Removed Columns"[Column1]), "Column1", "Column2"),
    #"Removed Columns1" = Table.RemoveColumns(#"Pivoted Column",{"Round Up"})
in
    #"Removed Columns1"

[Jdiep]

Hi Vijay,

Thank you so much. Been struggling, but you make it look easy 😉

Regards,

Jur