Transforming wide table to long table in Power Query

[Anonymous]

ID	Name	Region	Do you love Economics	Economics comments	Do you love Mathematics	Math Comments	Do you love Science	Science comments	Do you love English	English comments
1	John	TX	Yes	Valuable content	NO	It is hard	No	Not enough motivation	Yes	Easy
2	Jayden	MN	No	Teacher is boring	Yes	Easy	Yes	I need it to become an engineer	Yes	Speak 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.

ID	Name	Region	Questions	Response	Comments
1	John	TX	Do you love Economics	Yes	Valuable content
1	John	TX	Do you love Mathematics	No	It is hard
1	John	TX	Do you love Science	No	Not enough motivation
1	John	TX	Do you love English	Yes	Easy
2	Jayden	MN	Do you love Economics	No	Teacher is boring
2	Jayden	MN	Do you love Mathematics	Yes	Easy
2	Jayden	MN	Do you love Science	Yes	I need it to become an engineer
2	Jayden	MN	Do you love English	Yes	Speak it daily

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

Thanks

[Ashish_Mathur · Accepted solution]

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]

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]

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

[Ahmedx]

I worked on the code and wrote more optimized code

[Ahmedx]

pls try

[Anonymous]

The records for the Question column are not correct. Thanks

[Ahmedx]

sorry? pls try again

[Ahmedx]

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

[Anonymous]

I have added the data to the post.

[miTutorials]

In Power Query Editor, Select the columns that you dont want to unpivot (Name, region etc). Right click and choose unpivot other columns.

There is a detailed tutorial on the Pivot and Unpivot feature do check it out.

Pivot/Unpivot PowerBI Feature explained | Power Query | MiTutorials - YouTube

[Anonymous]

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