Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
2 years ago
Solved

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...
  • Ashish_Mathur's avatar
    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.