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.
Anonymous
2 years agoNot applicable
The records for the Question column are not correct. Thanks
Ahmedx
Super User
2 years agosorry? pls try again