Excel BI Power Query Challenge 106

ExcelBI106Solution.pq

let
  Source = Excel.CurrentWorkbook(){[Name = "Table2"]}[Content], 
  #"Promoted Headers" = Table.PromoteHeaders(Source, [PromoteAllScalars = true]), 
  #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(
    #"Promoted Headers", 
    {"Class"}, 
    "Attribute", 
    "Value"
  ), 
  #"Extracted Text Before Delimiter" = Table.TransformColumns(
    #"Unpivoted Other Columns", 
    {{"Attribute", each Text.BeforeDelimiter(_, "_"), type text}}
  ), 
  #"Break Out Student" = Table.AddColumn(
    #"Extracted Text Before Delimiter", 
    "Student", 
    each try if Text.Length([Value]) > 3 then [Value] else null otherwise null
  ), 
  #"Filled Down" = Table.FillDown(#"Break Out Student", {"Student"}), 
  #"Removed Other Columns" = Table.SelectColumns(
    #"Filled Down", 
    {"Class", "Attribute", "Value", "Student"}
  ), 
  #"Filtered Out Student Names" = Table.SelectRows(
    #"Removed Other Columns", 
    each (
      [Value]
        <> "Ashley" and [Value]
        <> "Betty" and [Value]
        <> "David" and [Value]
        <> "James" and [Value]
        <> "Mark" and [Value]
        <> "Mary" and [Value]
        <> "Mike" and [Value]
        <> "Nancy" and [Value]
        <> "Robert" and [Value]
        <> "Sarah"
    )
  ), 
  #"Pivoted Column" = Table.Pivot(
    #"Filtered Out Student Names", 
    List.Distinct(#"Filtered Out Student Names"[Attribute]), 
    "Attribute", 
    "Value"
  ), 
  #"Added Best of 3 Total" = Table.AddColumn(
    #"Pivoted Column", 
    "Best of 3 Total", 
    each List.Sum(
      List.RemoveFirstN(List.Sort({[Sub1], [Sub2], [Sub3], [Sub4], [Sub5]}, Order.Ascending), 2)
    ), 
    type number
  ), 
  #"Sorted Rows" = Table.Sort(
    #"Added Best of 3 Total", 
    {{"Class", Order.Ascending}, {"Best of 3 Total", Order.Descending}}
  ), 
  #"Grouped All by Class" = Table.Group(
    #"Sorted Rows", 
    {"Class"}, 
    {
      {
        "All", 
        each _, 
        type table [
          Class = nullable number, 
          Student = nullable text, 
          Sub1 = nullable number, 
          Sub2 = nullable number, 
          Sub3 = nullable number, 
          Sub4 = nullable number, 
          Sub5 = nullable number, 
          Total Top 3 = number
        ]
      }
    }
  ), 
  #"Added Rank Index" = Table.AddColumn(
    #"Grouped All by Class", 
    "Ranked", 
    each Table.AddIndexColumn([All], "Rank", 1, 1)
  ), 
  #"Removed Other Columns Again" = Table.SelectColumns(#"Added Rank Index", {"Ranked"}), 
  #"Expanded Ranked" = Table.ExpandTableColumn(
    #"Removed Other Columns Again", 
    "Ranked", 
    {"Class", "Student", "Sub1", "Sub2", "Sub3", "Sub4", "Sub5", "Total Top 3", "Rank"}, 
    {"Class", "Student", "Sub1", "Sub2", "Sub3", "Sub4", "Sub5", "Total Top 3", "Rank"}
  )
in
  #"Expanded Ranked"