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

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"

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