Forum Discussion

mmv's avatar
mmv
Frequent Visitor
1 year ago
Solved

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

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):

IDQuestion 1_1_2_3_4
1Value 1    
2 Value 2   
3  Value 3  
4Value 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:

IDQuestion 1
1Value 1
2Value 2
3Value 3
4Value 1Value 4
5Value 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:

IDQuestion 1
1Value 1
2Value 2
3Value 3
4Value 1
4Value 4
5Value 5

 

Is there a way to do this?

Need Help
  • Cookistador's avatar
    Cookistador
    1 year ago

    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"

     

  • SundarRaj's avatar
    SundarRaj
    1 year ago

    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