topic Re: Get forms data into structured lines in Power Query

Get forms data into structured lines

NumeroENAP — Wed, 06 Nov 2019 20:38:06 GMT

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

Re: Get forms data into structured lines

Anonymous — Wed, 06 Nov 2019 23:05:23 GMT

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"

Re: Get forms data into structured lines

artemus — Wed, 06 Nov 2019 23:07:17 GMT

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

Re: Get forms data into structured lines

NumeroENAP — Thu, 07 Nov 2019 15:39:17 GMT

You're a genius angel. Thanks a lot!

Re: Get forms data into structured lines

Anonymous — Thu, 07 Nov 2019 17:35:10 GMT

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,