Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago

Rank three separate columns in one table in Power Query

I have a small table of data:

Key    Count of Members     Engagement Ratio      Months Active

12                  200                 0.34              10.17

26                  479                 0.61              42.25

...

 

I rank each of the three  value columns by grouping by AllRows, sort descending, add Index, expand the Key. Boom.

I have to do this three times for each column. At the moment, I am using three separate tables to do this, then merging them back together. Urgh.

I know how to do half of this in a single table - when I want to group the second column, I reference the Source step as the previous step. However, once i've grouped all three - i'm then stuck, because I don't know how to return the three rank values. 

 

Is this possible? Or do I have to do it in three tables? 

Need Help

3 Replies

  • DataInsights's avatar
    DataInsights
    Icon for Super User rankSuper User
    5 years ago

    Anonymous,

     

    If you are ranking without subgroupings, this should work (each index is for the entire table).

     

    let
  Source = Table.FromRows(
    Json.Document(
      Binary.Decompress(
        Binary.FromText(
          "JYtBDgAhCAP/0rMhUEXjWwj//4awe2mmmTYCRgxQtVLmqjQVO8gR4K66zm21rZlC/9Tsvf8vb0TmAw==",
          BinaryEncoding.Base64
        ),
        Compression.Deflate
      )
    ),
    let
      _t = ((type nullable text) meta [Serialized.Text = true])
    in
      type table [
        Key = _t,
        #"Count of Members" = _t,
        #"Engagement Ratio" = _t,
        #"Months Active" = _t
      ]
  ),
  ChangeType = Table.TransformColumnTypes(
    Source,
    {
      {"Key", Int64.Type},
      {"Count of Members", Int64.Type},
      {"Engagement Ratio", type number},
      {"Months Active", type number}
    }
  ),
  SortRows = Table.Sort(ChangeType, {{"Count of Members", Order.Ascending}}),
  AddIndex = Table.AddIndexColumn(SortRows, "Index", 1, 1, Int64.Type),
  RenameColumn = Table.RenameColumns(AddIndex, {{"Index", "Count of Members Index"}}),
  SortRows2 = Table.Sort(RenameColumn, {{"Engagement Ratio", Order.Ascending}}),
  AddIndex2 = Table.AddIndexColumn(SortRows2, "Index", 1, 1, Int64.Type),
  RenameColumn2 = Table.RenameColumns(AddIndex2, {{"Index", "Engagement Ratio Index"}}),
  SortRows3 = Table.Sort(RenameColumn2, {{"Months Active", Order.Ascending}}),
  AddIndex3 = Table.AddIndexColumn(SortRows3, "Index", 1, 1, Int64.Type),
  RenameColumn3 = Table.RenameColumns(AddIndex3, {{"Index", "Months Active Index"}})
in
  RenameColumn3

     

    Sample data:

     

     

    Result:

     

     

    If you need to rank by subgroupings, see the article below:

     

    https://blog.crossjoin.co.uk/2015/05/11/nested-calculations-in-power-query/ 

    • Anonymous's avatar
      Anonymous
      Not applicable
      5 years ago

      Hi DataInsights this does not include the group by which I have to perform to get the Rank (index). Because there could be numerous entries with the same value, they would have the same rank. So I need to expand each index back into the table at the end. 

      • DataInsights's avatar
        DataInsights
        Icon for Super User rankSuper User
        5 years ago

        Anonymous,

         

        Would you be able to provide a more detailed example, including the Group By column?