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.
Ahmedx
Super User
2 years agoI worked on the code and wrote more optimized code