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
Super User
2 years ago

It's hard to suggest something without sample data that mirrors what your real situation is.

 

What is the part of my example that doesn't work for you? Listing the column names?

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