Forum Discussion

kleetus51's avatar
kleetus51
Helper I
1 year ago
Solved

Power Query - Rank by group with sort direction variable

I have a table of KPIs by customer. I need to add a Rank column by KPI, but I need it to sort each KPI differently depending on another column. Is it possible to get the following values in my Rank c...
Need Help
  • Fowmy's avatar
    Fowmy
    1 year ago

    kleetus51 

    Sort it this way:

    let
  Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content], 
  #"Changed Type" = Table.TransformColumnTypes(
    Source, 
    {{"Customer", type text}, {"KPI", type text}, {"Value", type number}, {"Ranking Direction", type text}}
  ), 
  #"Grouped Rows" = Table.Group(
    #"Changed Type", 
    {"KPI"}, 
    {
      {
        "Detail", 
        each Table.AddRankColumn(
          _, 
          "Rank", 
          if _[Ranking Direction]{0} = "Descending" then {{"Value", Order.Descending}} else {{"Value", Order.Ascending}}
        )
      }
    }
  ), 
  Custom1 = Table.Combine(#"Grouped Rows"[Detail])
in
  Custom1
Fowmy's avatar
Fowmy
Super User
1 year ago

kleetus51 

Sort it this way:

let
  Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content], 
  #"Changed Type" = Table.TransformColumnTypes(
    Source, 
    {{"Customer", type text}, {"KPI", type text}, {"Value", type number}, {"Ranking Direction", type text}}
  ), 
  #"Grouped Rows" = Table.Group(
    #"Changed Type", 
    {"KPI"}, 
    {
      {
        "Detail", 
        each Table.AddRankColumn(
          _, 
          "Rank", 
          if _[Ranking Direction]{0} = "Descending" then {{"Value", Order.Descending}} else {{"Value", Order.Ascending}}
        )
      }
    }
  ), 
  Custom1 = Table.Combine(#"Grouped Rows"[Detail])
in
  Custom1