Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
2 years ago
Solved

Transforming wide table to long table in Power Query

IDNameRegionDo you love EconomicsEconomics commentsDo you love MathematicsMath CommentsDo you love ScienceScience commentsDo you love EnglishEnglish comments
1JohnTXYesValuable contentNOIt is hardNoNot enough motivationYesEasy
2JaydenMNNoTeacher is boringYesEasyYesI need it to become an engineerYesSpeak it daily
           

Hello,

I have to report on a survey data which comes in the wide form as shown below: (Wehave the raw data in the table above)

 

I want to transform the data to the long form as shown in the picture below (that is, keep all the initial 3 columns as they are but transform the respective questions, answers and comments into 3 other columns). The data for the result is below.

IDNameRegionQuestionsResponseComments
1JohnTXDo you love EconomicsYesValuable content
1JohnTXDo you love MathematicsNoIt is hard
1JohnTXDo you love ScienceNoNot enough motivation
1JohnTXDo you love EnglishYesEasy
2JaydenMNDo you love EconomicsNoTeacher is boring
2JaydenMNDo you love MathematicsYesEasy
2JaydenMNDo you love ScienceYesI need it to become an engineer
2JaydenMNDo you love EnglishYesSpeak it daily

Please, can someone share an insight on how to about this?

Thanks

 

 

Need Help
  • Ashish_Mathur's avatar
    Ashish_Mathur
    2 years ago

    Hi,

    This M code works

    let
    Source = Excel.CurrentWorkbook(){[Name="Data"]}[Content],
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(Source, {"ID", "Name", "Region"}, "Subject", "Value"),
    #"Added Custom" = Table.AddColumn(#"Unpivoted Other Columns", "Custom", each if Text.StartsWith([Subject], "Do you",Comparer.OrdinalIgnoreCase) then "Question" else "Comments"),
    #"Replaced Value" = Table.ReplaceValue(#"Added Custom"," comments","",Replacer.ReplaceText,{"Subject"}),
    #"Replaced Value2" = Table.ReplaceValue(#"Replaced Value"," Comments","",Replacer.ReplaceText,{"Subject"}),
    #"Replaced Value3" = Table.ReplaceValue(#"Replaced Value2","Mathematics","Math",Replacer.ReplaceText,{"Subject"}),
    #"Replaced Value1" = Table.ReplaceValue(#"Replaced Value3","Do you love ","",Replacer.ReplaceText,{"Subject"}),
    #"Pivoted Column" = Table.Pivot(#"Replaced Value1", List.Distinct(#"Replaced Value1"[Custom]), "Custom", "Value")
in
    #"Pivoted Column"

    Hope this helps.

     

11 Replies

    • Anonymous's avatar
      Anonymous
      Not applicable
      2 years ago

      That will not solve the problem because the unpivoting will result in only two columns for attribute and values but in my case, I need to end up with thre columns, Question, Response and comments

  • Ahmedx's avatar
    Ahmedx
    Icon for Super User rankSuper User
    2 years ago

    share an example, it’s impossible to copy from a picture

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      2 years ago

      I have added the data to the post.

  • Ashish_Mathur's avatar
    Ashish_Mathur
    Icon for Super User rankSuper User
    2 years ago

    Hi,

    This M code works

    let
    Source = Excel.CurrentWorkbook(){[Name="Data"]}[Content],
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(Source, {"ID", "Name", "Region"}, "Subject", "Value"),
    #"Added Custom" = Table.AddColumn(#"Unpivoted Other Columns", "Custom", each if Text.StartsWith([Subject], "Do you",Comparer.OrdinalIgnoreCase) then "Question" else "Comments"),
    #"Replaced Value" = Table.ReplaceValue(#"Added Custom"," comments","",Replacer.ReplaceText,{"Subject"}),
    #"Replaced Value2" = Table.ReplaceValue(#"Replaced Value"," Comments","",Replacer.ReplaceText,{"Subject"}),
    #"Replaced Value3" = Table.ReplaceValue(#"Replaced Value2","Mathematics","Math",Replacer.ReplaceText,{"Subject"}),
    #"Replaced Value1" = Table.ReplaceValue(#"Replaced Value3","Do you love ","",Replacer.ReplaceText,{"Subject"}),
    #"Pivoted Column" = Table.Pivot(#"Replaced Value1", List.Distinct(#"Replaced Value1"[Custom]), "Custom", "Value")
in
    #"Pivoted Column"

    Hope this helps.

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      2 years ago

      This solution worked for this sample data. Thanks so much.

      Unfortunately, it did not work for the project that I needed it for. I got the following error when I tried the pivoting step:

      Expression Error: We cannot apply operator < to types List and List

      Details: 

      Operator=<

      Left =[List]

      Right=[List]

       

      How do i resolve this?

      • Ashish_Mathur's avatar
        Ashish_Mathur
        Icon for Super User rankSuper User
        2 years ago

        You are welcome.  I cannot know the reason just by looking at that line of error.  Try to debug it yourself.