Forum Discussion

LeroyPaul's avatar
LeroyPaul
Icon for Helper I rankHelper I
5 years ago
Solved

Ranking by Category in M

Hello,

I am having difficulties with the M and I would like to know if anyone can help me

 

I have a table that looks like this :

 

IndexSalesCountryName
1500France

X1

2100BelgiumX2
3120FranceX3
470ItaliaX4
5250FranceX5

 

And I would like to classify it like this by adding a formula in M under Power Query

 

IndexSalesCountryNameRank
1500France

X1

1

2100BelgiumX21
3120FranceX33
470ItaliaX41
5250FranceX52

 

I found this on the internet but I couldn't adapt it to my problem (https://blog.crossjoin.co.uk/2015/05/11/nested-calculations-in-power-query/)

 

Thank you in advance for your help

Paul

 

Need Help
Show and Tell
  • v-kelly-msft's avatar
    v-kelly-msft
    5 years ago

    Hi  LeroyPaul ，

     

    Using below M codes:

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUTI1MACSbkWJecmpQEaEoVKsTrSSEZBpCJZxSs1JzyzNBUkZgaWMQVJGqJqMwTImQKY5SMKzJDEnMxEkYQKWMAUyjUxRtZgqxcYCAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Index = _t, Sales = _t, Country = _t, Name = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Index", Int64.Type}, {"Sales", Int64.Type}, {"Country", type text}, {"Name", type text}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"Country"}, {{"allrows", each _, type table [Index=nullable number, Sales=nullable number, Country=nullable text, Name=nullable text]}}),
    RankFunction = (tabletorank as table) as table =>
     let
      SortRows = Table.Sort(tabletorank,{{"Sales", Order.Descending}}),
      AddIndex = Table.AddIndexColumn(SortRows, "Rank", 1, 1)
     in
      AddIndex,
    Custom1 = Table.TransformColumns(#"Grouped Rows", {"allrows", each RankFunction(_)}),
    #"Expanded allrows" = Table.ExpandTableColumn(Custom1, "allrows", {"Sales", "Name", "Rank"}, {"allrows.Sales", "allrows.Name", "allrows.Rank"})
in
    #"Expanded allrows"

    And you will see:

     

    Check my .pbix file attached for reference.

     

    Best Regards,
    Kelly

    Did I answer your question? Mark my reply as a solution!

3 Replies

    • Jakinta's avatar
      Jakinta
      Icon for Solution Sage rankSolution Sage
      5 years ago

      This should help.

      let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUTI1MACSbkWJecmpQEaEoVKsTrSSEZBpCJZxSs1JzyzNBUkZgaWMQVJGqJqMwTImQKY5SMKzJDEnMxEkYQKWMAUyjUxRtZgqxcYCAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Index = _t, Sales = _t, Country = _t, Name = _t]),
    Grouped = Table.Group(Source, {"Country"}, {{"Gr", each Table.AddIndexColumn(Table.Sort(_,{"Sales" ,Order.Descending}) , "Rank",1,1), type table }}),
    Removed = Table.RemoveColumns(Grouped,{"Country"}),
    Expanded = Table.ExpandTableColumn(Removed, "Gr", {"Index", "Sales", "Country", "Name", "Rank"}, {"Index", "Sales", "Country", "Name", "Rank"}),
    Sorted = Table.Sort(Expanded,{{"Index", Order.Ascending}})
in
    Sorted
  • v-kelly-msft's avatar
    v-kelly-msft
    Icon for Community Support rankCommunity Support
    5 years ago

    Hi  LeroyPaul ，

     

    Using below M codes:

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUTI1MACSbkWJecmpQEaEoVKsTrSSEZBpCJZxSs1JzyzNBUkZgaWMQVJGqJqMwTImQKY5SMKzJDEnMxEkYQKWMAUyjUxRtZgqxcYCAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Index = _t, Sales = _t, Country = _t, Name = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Index", Int64.Type}, {"Sales", Int64.Type}, {"Country", type text}, {"Name", type text}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"Country"}, {{"allrows", each _, type table [Index=nullable number, Sales=nullable number, Country=nullable text, Name=nullable text]}}),
    RankFunction = (tabletorank as table) as table =>
     let
      SortRows = Table.Sort(tabletorank,{{"Sales", Order.Descending}}),
      AddIndex = Table.AddIndexColumn(SortRows, "Rank", 1, 1)
     in
      AddIndex,
    Custom1 = Table.TransformColumns(#"Grouped Rows", {"allrows", each RankFunction(_)}),
    #"Expanded allrows" = Table.ExpandTableColumn(Custom1, "allrows", {"Sales", "Name", "Rank"}, {"allrows.Sales", "allrows.Name", "allrows.Rank"})
in
    #"Expanded allrows"

    And you will see:

     

    Check my .pbix file attached for reference.

     

    Best Regards,
    Kelly

    Did I answer your question? Mark my reply as a solution!