Forum Discussion
Anonymous
2 years agoNot applicable
Transforming wide table to long table in Power Query
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 Ye...
- 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.
miTutorials
Super User
2 years agoIn 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
2 years agoNot applicable
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