Power BI - Transforming data - Join columns and divide into rows in case of multiple entries

[mmv]

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?

[Cookistador · Accepted 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.

1. 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")

2. Unpivot Columns: Right-click on any of the selected columns. In the context menu, choose "Unpivot Columns".

3. Rename Columns (Optional but Recommended):

   • The "Attribute" column will now contain the original column names (e.g., "Question 1_1"). So this colum can be deleted
   • The "Value" column will contain the actual answer values. Rename this to your desired question column name (e.g., "Question 1").

4. 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:

   • Click the dropdown arrow on the "Question 1" column header.
   • Deselect "(null)" or "(blank)" (depending on how your survey tool represents unselected options).
   • Click "OK".

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"

[v-pagayam-msft]

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.

[v-pagayam-msft]

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.

[v-pagayam-msft]

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.

[SundarRaj · Accepted solution]

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

[Cookistador · Accepted 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.

1. 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")

2. Unpivot Columns: Right-click on any of the selected columns. In the context menu, choose "Unpivot Columns".

3. Rename Columns (Optional but Recommended):

   • The "Attribute" column will now contain the original column names (e.g., "Question 1_1"). So this colum can be deleted
   • The "Value" column will contain the actual answer values. Rename this to your desired question column name (e.g., "Question 1").

4. 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:

   • Click the dropdown arrow on the "Question 1" column header.
   • Deselect "(null)" or "(blank)" (depending on how your survey tool represents unselected options).
   • Click "OK".

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"