Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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 |
Solved! Go to 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.
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.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
111 | |
95 | |
80 | |
68 | |
59 |
User | Count |
---|---|
150 | |
119 | |
104 | |
87 | |
67 |