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 column based on the direction and customer?

 

CustomerKPIValueRanking DirectionRank
WFJPYAverage Days to Pay9.44Ascending1
IYKRJAverage Days to Pay13.88Ascending2
IBZTBAverage Days to Pay22.94Ascending3
LSOFUAverage Days to Pay36.96Ascending4
VTTFHAverage Days to Pay37.27Ascending5
GAVOKAverage Days to Pay44.25Ascending6
HQPQOAverage Days to Pay44.27Ascending7
GAMOJAverage Days to Pay46.43Ascending8
LMMEXAverage Days to Pay48.04Ascending9
QFYNFAverage Days to Pay54.61Ascending10
TGQFLAverage Days to Pay73.30Ascending11
OSZLLAverage Days to Pay77.65Ascending12
EZPGIAverage Days to Pay80.84Ascending13
IYKRJSales353,328.04Descending1
HQPQOSales324,707.65Descending2
TGQFLSales300,375.46Descending3
WFJPYSales288,050.65Descending4
OSZLLSales271,172.62Descending5
GAVOKSales269,026.91Descending6
IBZTBSales239,993.21Descending7
LMMEXSales233,063.59Descending8
GAMOJSales199,614.27Descending9
VTTFHSales189,326.97Descending10
LSOFUSales156,850.82Descending11
EZPGISales138,739.33Descending12
QFYNFSales122,803.12Descending13
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

2 Replies

  • 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