Forum Discussion

VGuruprasad2386's avatar
VGuruprasad2386
Regular Visitor
2 years ago
Solved

Unpivots

Hi all,     I have 35 questions for which I have a results same questions was rolled out last year I have results of that too, I have to compare and show the results to the management I tried unpi...
Need Help
  • AlexisOlson's avatar
    AlexisOlson
    2 years ago

    The code is very similar.

    let
    Source = Excel.Workbook(File.Contents("C:\Users\aolson\Downloads\Sample raw_data.xlsx"), null, true),
    Sheet1_Sheet = Source{[Item="Sheet1",Kind="Sheet"]}[Data],
    #"Promoted Headers" = Table.PromoteHeaders(Sheet1_Sheet, [PromoteAllScalars=true]),
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Promoted Headers", {"Sl no", "Gender", "State", "Zone", "Department"}, "Question", "Value"),
    #"Added Custom" = Table.AddColumn(#"Unpivoted Other Columns", "Year", each if Text.Contains([Question], "_") then "Last Year" else "This Year", type text),
    #"Extracted Text Before Delimiter" = Table.TransformColumns(#"Added Custom", {{"Question", each Text.BeforeDelimiter(_, "_"), type text}}),
    #"Pivoted Column" = Table.Pivot(#"Extracted Text Before Delimiter", List.Distinct(#"Extracted Text Before Delimiter"[Year]), "Year", "Value"),
    #"Changed Type1" = Table.TransformColumnTypes(#"Pivoted Column",{{"This Year", Int64.Type}, {"Last Year", type number}, {"Sl no", Int64.Type}, {"Gender", type text}, {"State", type text}, {"Zone", type text}, {"Department", type text}, {"Question", type text}})
in
    #"Changed Type1"

     

    See attached file.

AlexisOlson's avatar
AlexisOlson
Icon for Super User rankSuper User
2 years ago

Is this the sort of transformation you're looking for?


From this:

 

To this:

 

Sample code for the above:

let
  Source = Table.FromRows(
    {
      {1, "Male", "North", "Active", 1, 2, 3, 4, 2, 4, 6, 8}, 
      {2, "Female", "Central", "Active", 3, 5, 7, 9, 2, 4, 6, 8}, 
      {3, "Male", "South", "Inactive", 0, 2, 4, 6, 0, 4, 8, 12}
    }, 
    type table [
      ID = number, Gender = text, Zone = text, Status = text, 
      a = number, b = number, c = number, d = number, 
      A = number, B = number, C = number, D = number
    ]
  ),
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(Source, {"ID", "Gender", "Zone", "Status"}, "Column", "Value"),
    #"Added Custom" = Table.AddColumn(#"Unpivoted Other Columns", "Year", each if List.Contains({"a","b","c","d"}, [Column]) then "This Year" else if List.Contains({"A","B","C","D"}, [Column]) then "Last Year" else null, type text),
    #"Uppercased Text" = Table.TransformColumns(#"Added Custom",{{"Column", Text.Upper, type text}}),
    #"Pivoted Column" = Table.Pivot(#"Uppercased Text", List.Distinct(#"Uppercased Text"[Year]), "Year", "Value")
in
  #"Pivoted Column"