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

     

Ashish_Mathur's avatar
Ashish_Mathur
Icon for Super User rankSuper User
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's avatar
Anonymous
Not applicable
2 years ago

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's avatar
    Ashish_Mathur
    Icon for Super User rankSuper User
    2 years ago

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