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 unpivoting both set of questions when i put it in the metrics results shows incorrect please help the solve this issue. Below googlesheet for the dataset

https://docs.google.com/spreadsheets/d/1KVclPSCHdvxHyHkIBxfiUMFDhOqxg2kb2fOgu5qioWA/edit?usp=sharing 

1. from column E to AM is this years results

2. AN to BW last year result

 

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.

5 Replies

  • AlexisOlson's avatar
    AlexisOlson
    Super 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"

     

  • VGuruprasad2386's avatar
    VGuruprasad2386
    Regular Visitor
    2 years ago

     

    Hi AlexisOlson,

     

    Thanks for the code, sorry to say that I will not be able use this function since there are few other columns  such as departments and subdepartments which has to be mapped and they are more than thousands, so can you suggest some other option