Get forms data into structured lines

[NumeroENAP]

Hi,

This is an exemple of what I have :

Questions	Answers
Id	1
Question 1	A
Question 2	A
Question 3	A
Id	2
Question 1	B
Question 2	B
Question 3	B
Id	3
Question 1	C
Question 2	C
Question 3	C

And this is what I want to get :

ID	Question1	Question2	Question3
1	A	A	A
2	B	B	B
3	C	C	C

Thank you

[artemus · Accepted solution]

Something like this (substitute YourTable with your table name):

let
    Source = YourTable,
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Questions", type text}, {"Answers", type text}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"Questions"}, {{"Rows", each [Answers], type {text}}}),
    #"Transposed Table" = Table.Transpose(#"Grouped Rows"),
    #"Promoted Headers" = Table.PromoteHeaders(#"Transposed Table", [PromoteAllScalars=true]),
    #"Changed Type1" = Table.TransformColumnTypes(#"Promoted Headers",{{"Id", type any}, {"Question 1", type any}, {"Question 2", type any}, {"Question 3", type any}}),
    Custom1 = Table.FromRows(List.Zip(Record.FieldValues(#"Changed Type1"{0})), Table.ColumnNames(#"Changed Type1"))
in
    Custom1

[Anonymous]

Hi @artemus.,

very interesting solution, I did not even think into this direction.

I think you can slightly modify it to something like this to reduce compute-greedy transformations.

let 
    Source=...    
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Questions", type text}, {"Answers", type text}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"Questions"}, {{"Rows", each [Answers], type {text}}}),
    Output = Table.FromColumns(#"Grouped Rows"[Rows], #"Grouped Rows"[Questions])
in
    Output

Kind regards,

JB

[NumeroENAP]

You're a genius angel. Thanks a lot!

[Anonymous]

Presuming that you are power querying in excel

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Questions", type text}, {"Answers", type any}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "ID", each if [Questions] = "Id" then [Answers] else null),
    #"Filled Down" = Table.FillDown(#"Added Custom",{"ID"}),
    #"Filtered Rows" = Table.SelectRows(#"Filled Down", each ([Questions] <> "Id")),
    #"Pivoted Column" = Table.Pivot(#"Filtered Rows", List.Distinct(#"Filtered Rows"[Questions]), "Questions", "Answers", List.Min)
in
    #"Pivoted Column"