Power Query - Rank by group with sort direction variable

[kleetus51]

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?

Customer	KPI	Value	Ranking Direction	Rank
WFJPY	Average Days to Pay	9.44	Ascending	1
IYKRJ	Average Days to Pay	13.88	Ascending	2
IBZTB	Average Days to Pay	22.94	Ascending	3
LSOFU	Average Days to Pay	36.96	Ascending	4
VTTFH	Average Days to Pay	37.27	Ascending	5
GAVOK	Average Days to Pay	44.25	Ascending	6
HQPQO	Average Days to Pay	44.27	Ascending	7
GAMOJ	Average Days to Pay	46.43	Ascending	8
LMMEX	Average Days to Pay	48.04	Ascending	9
QFYNF	Average Days to Pay	54.61	Ascending	10
TGQFL	Average Days to Pay	73.30	Ascending	11
OSZLL	Average Days to Pay	77.65	Ascending	12
EZPGI	Average Days to Pay	80.84	Ascending	13
IYKRJ	Sales	353,328.04	Descending	1
HQPQO	Sales	324,707.65	Descending	2
TGQFL	Sales	300,375.46	Descending	3
WFJPY	Sales	288,050.65	Descending	4
OSZLL	Sales	271,172.62	Descending	5
GAVOK	Sales	269,026.91	Descending	6
IBZTB	Sales	239,993.21	Descending	7
LMMEX	Sales	233,063.59	Descending	8
GAMOJ	Sales	199,614.27	Descending	9
VTTFH	Sales	189,326.97	Descending	10
LSOFU	Sales	156,850.82	Descending	11
EZPGI	Sales	138,739.33	Descending	12
QFYNF	Sales	122,803.12	Descending	13

[ThxAlot]

Isn't a calculated column easy enough?

[Fowmy · Accepted solution]

@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