Starting December 3, join live sessions with database experts and the Microsoft product team to learn just how easy it is to get started
Learn moreGet certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now
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
Solved! Go to 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"
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"
Hi Vijay,
Thank you so much. Been struggling, but you make it look easy 😉
Regards,
Jur
Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.
User | Count |
---|---|
35 | |
17 | |
12 | |
11 | |
9 |
User | Count |
---|---|
45 | |
27 | |
16 | |
14 | |
13 |