Custom columns that return values with same ID

Question

Hey all,&nbsp;I'm dealing with some survey data and hoping I could get some help here.&nbsp;I would like to be able to split out any given response value into a column, and then apply it to each row with the same ID. So make something like:&nbsp;IDSurvey TitleResponse Value1What color?blue1What number?21Supplier name?Durant's1What location?patio2What number?22Supplier Name?FurnCo2What color?red2What location?basement&nbsp;Looks something like:&nbsp;IDSurvey TitleResponse ValueColorSupplier1What color?blueblueDurant's1What number?2blueDurant's1Supplier name?Durant'sblueDurant's1What location?patioblueDurant's2What number?2redFurnCo2Supplier Name?FurnCoredFurnCo2What color?redredFurnCo2What location?basementredFurnCo&nbsp;

anonymous · Accepted Answer

Hi&nbsp;Anonymous&nbsp;
You can refer to the following example.
Create two "Blank Query" in power query

&nbsp;
Then put the following code to Advanced Editor in power query
let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUQrPSCxRSM7PyS+yB/KSckpTlWJ1kKTySnOTUsFyRnCJ4NKCgpzM1CKFvMTcVJCUS2lRYl5JTKmBgZF5Mar+nPzkxJLM/DyQsgIQCyxthMt4I2Tj/aDGu5UW5TmjaYQ7uSg1BVUG2cakxOLU3NS8EqXYWAA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [ID = _t, #"Survey Title" = _t, #"Response Value" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"ID", Int64.Type}, {"Survey Title", type text}, {"Response Value", type text}})
in
    #"Changed Type"let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUQrPSCxRSM7PyS+yB/KSckpTlWJ1kKTySnOTUsFyRnCJ4NKCgpzM1CKFvMTcVJCUS2lRYl5JTKmBgZF5Mar+nPzkxJLM/DyQsgIQCyxthMt4I2Tj/aDGu5UW5TmjaYQ7uSg1BVUG2cakxOLU3NS8EqXYWAA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [ID = _t, #"Survey Title" = _t, #"Response Value" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"ID", Int64.Type}, {"Survey Title", type text}, {"Response Value", type text}}),
    #"Capitalized Each Word" = Table.TransformColumns(#"Changed Type",{{"Survey Title", Text.Proper, type text}}),
    #"Pivoted Column" = Table.Pivot(#"Capitalized Each Word", List.Distinct(#"Capitalized Each Word"[#"Survey Title"]), "Survey Title", "Response Value"),
    #"Merged Queries" = Table.NestedJoin(#"Pivoted Column", {"ID"}, #"Query1", {"ID"}, "Query1", JoinKind.LeftOuter),
    #"Expanded Table (2)" = Table.ExpandTableColumn(#"Merged Queries", "Query1", {"Survey Title", "Response Value"}, {"Survey Title", "Response Value"}),
    #"Reordered Columns" = Table.ReorderColumns(#"Expanded Table (2)",{"ID", "Survey Title", "Response Value", "What Color?", "What Number?", "Supplier Name?", "What Location?"})
in
    #"Reordered Columns"
Output

&nbsp;
Best Regards!
Yolo Zhu
If this post&nbsp;helps, then please consider&nbsp;Accept it as the solution&nbsp;to help the other members find it more quickly.

Forum Discussion

Custom columns that return values with same ID

1 Reply

Related content

Creating custom columns keeps returning a structured Table column

Custom Column

Custom Column

Return Column Name if Zero Value

If Statement Calculated Column Returning Incorrectly

ID	Survey Title	Response Value
1	What color?	blue
1	What number?	2
1	Supplier name?	Durant's
1	What location?	patio
2	What number?	2
2	Supplier Name?	FurnCo
2	What color?	red
2	What location?	basement

ID	Survey Title	Response Value	Color	Supplier
1	What color?	blue	blue	Durant's
1	What number?	2	blue	Durant's
1	Supplier name?	Durant's	blue	Durant's
1	What location?	patio	blue	Durant's
2	What number?	2	red	FurnCo
2	Supplier Name?	FurnCo	red	FurnCo
2	What color?	red	red	FurnCo
2	What location?	basement	red	FurnCo