Power BI is turning 10, and we’re marking the occasion with a special community challenge. Use your creativity to tell a story, uncover trends, or highlight something unexpected.
Get startedJoin us for an expert-led overview of the tools and concepts you'll need to become a Certified Power BI Data Analyst and pass exam PL-300. Register now.
Hi there,
I have received data from a survey tool that needs to be visualized. But I discovered a data issue where in multiple choice questions additional column was created for each answer.
As an example the data in the background would be provided this way(for one answered question):
ID | Question 1 | _1 | _2 | _3 | _4 |
1 | Value 1 | ||||
2 | Value 2 | ||||
3 | Value 3 | ||||
4 | Value 1 | Value 4 | |||
5 | Value 5 |
I can join all the columns together to have just one column with all the answers, however I have a problem with answers where multiple options have been selected as it would look like:
ID | Question 1 |
1 | Value 1 |
2 | Value 2 |
3 | Value 3 |
4 | Value 1Value 4 |
5 | Value 5 |
My idea/question is if there is a way to combine these answers into one column, and if there are multiple answers, separate them in multiple rows.
The ideal result would be:
ID | Question 1 |
1 | Value 1 |
2 | Value 2 |
3 | Value 3 |
4 | Value 1 |
4 | Value 4 |
5 | Value 5 |
Is there a way to do this?
Solved! Go to Solution.
Hi @mmv
You could achieve what you need by following these steps
This is generally the most straightforward and recommended approach for this type of data restructuring.
Select Relevant Columns: In the Power Query Editor, select the columns that represent the individual answer choices for your multiple-choice question "Question 1_1", "_1", "_2", "_3","_4")
Unpivot Columns: Right-click on any of the selected columns. In the context menu, choose "Unpivot Columns".
Rename Columns (Optional but Recommended):
Filter Out Blanks/Nulls: You'll likely have rows where no option was selected for a particular original column, resulting in blank or null values in the "Question 1" column. Filter these out:
This is the result
If you prefer, I gave you the M code to achieve this transformation
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUQpLzClNBTEQKFYnWskIyFCASSvAeBAMkjdGEoGoMUaRN4GLG2KoNIGrMkUxF1mNqVJsLAA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [ID = _t, #"Question 1" = _t, _1 = _t, _2 = _t, _3 = _t, _4 = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"ID", Int64.Type}, {"Question 1", type text}, {"_1", type text}, {"_2", type text}, {"_3", type text}, {"_4", type text}}),
#"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"ID"}, "Attribute", "Value"),
#"Removed Columns" = Table.RemoveColumns(#"Unpivoted Columns",{"Attribute"}),
#"Filtered Rows" = Table.SelectRows(#"Removed Columns", each ([Value] <> "" and [Value] <> " "))
in
#"Filtered Rows"
Hi @mmv , here's another solution you could look at. I'll attach the images for you to have a look at it. Let me if I understood your query. Thanks!
Here's the code:
let
Source = Excel.CurrentWorkbook(){[Name="Table2"]}[Content],
#"Changed Type" = Table.TransformColumnTypes(Source,{{"ID", Int64.Type}, {"Question 1", type text}, {"_1", type text}, {"_2", type text}, {"_3", type text}, {"_4", type text}}),
List = Table.ToRows(#"Changed Type"),
Nulls = List.Transform(List, each List.RemoveNulls(_)),
Check = List.Transform(Nulls, each List.Select(_, each not (try Text.Start(_,1))[HasError])),
Index = Table.AddIndexColumn(#"Changed Type"[[ID]],"Value",0,1),
Import = Table.TransformColumns(Index,{"Value", each Check{_}}),
FinTable = Table.ExpandListColumn(Import, "Value")
in
FinTable
Hi @mmv ,
Could you please confirm if the issue has been resolved on your end? If a solution has been found, it would be greatly appreciated if you could share your insights with the community.
Thank you for your understanding and assistance.
Hi @mmv ,
I wanted to follow up on our previous suggestions regarding the issue you are facing. We would like to hear back from you to ensure we can assist you further. If our response has addressed your query, please accept it as a solution and give a ‘Kudos’ so other members can easily find it.
Thank you.
Hi @mmv ,
Than you @SundarRaj and @Cookistador for the helpful response!
I wanted to follow up on our previous suggestions regarding the issue you are facing. We would like to hear back from you to ensure we can assist you further. If our response has addressed your query, please accept it as a solution and give a ‘Kudos’ so other members can easily find it.
Thank you.
Hi @mmv , here's another solution you could look at. I'll attach the images for you to have a look at it. Let me if I understood your query. Thanks!
Here's the code:
let
Source = Excel.CurrentWorkbook(){[Name="Table2"]}[Content],
#"Changed Type" = Table.TransformColumnTypes(Source,{{"ID", Int64.Type}, {"Question 1", type text}, {"_1", type text}, {"_2", type text}, {"_3", type text}, {"_4", type text}}),
List = Table.ToRows(#"Changed Type"),
Nulls = List.Transform(List, each List.RemoveNulls(_)),
Check = List.Transform(Nulls, each List.Select(_, each not (try Text.Start(_,1))[HasError])),
Index = Table.AddIndexColumn(#"Changed Type"[[ID]],"Value",0,1),
Import = Table.TransformColumns(Index,{"Value", each Check{_}}),
FinTable = Table.ExpandListColumn(Import, "Value")
in
FinTable
Hi @mmv
You could achieve what you need by following these steps
This is generally the most straightforward and recommended approach for this type of data restructuring.
Select Relevant Columns: In the Power Query Editor, select the columns that represent the individual answer choices for your multiple-choice question "Question 1_1", "_1", "_2", "_3","_4")
Unpivot Columns: Right-click on any of the selected columns. In the context menu, choose "Unpivot Columns".
Rename Columns (Optional but Recommended):
Filter Out Blanks/Nulls: You'll likely have rows where no option was selected for a particular original column, resulting in blank or null values in the "Question 1" column. Filter these out:
This is the result
If you prefer, I gave you the M code to achieve this transformation
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUQpLzClNBTEQKFYnWskIyFCASSvAeBAMkjdGEoGoMUaRN4GLG2KoNIGrMkUxF1mNqVJsLAA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [ID = _t, #"Question 1" = _t, _1 = _t, _2 = _t, _3 = _t, _4 = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"ID", Int64.Type}, {"Question 1", type text}, {"_1", type text}, {"_2", type text}, {"_3", type text}, {"_4", type text}}),
#"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"ID"}, "Attribute", "Value"),
#"Removed Columns" = Table.RemoveColumns(#"Unpivoted Columns",{"Attribute"}),
#"Filtered Rows" = Table.SelectRows(#"Removed Columns", each ([Value] <> "" and [Value] <> " "))
in
#"Filtered Rows"
This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.
Check out the June 2025 Power BI update to learn about new features.
User | Count |
---|---|
15 | |
11 | |
8 | |
8 | |
7 |
User | Count |
---|---|
15 | |
13 | |
9 | |
7 | |
6 |