Custom columns that return values with same ID

[Atleti]

Hey all,

I'm dealing with some survey data and hoping I could get some help here. 

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:

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

Looks something like:

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

[v-xinruzhu-msft · Accepted solution]

Hi @Atleti 

You can refer to the following example.

Create two "Blank Query" in power query

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

Best Regards!

Yolo Zhu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.