Add a list ranked by category using M Query.

Hi roxy12 ,

How about this:

Here the code in M:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("jZRba9xADIX/itjnwHg0Hl/mreTSC00KycJSQh60u5NYZGwHr5s2+fXV2NlQUoMNBhv8jXR0dOzb29Ul7yryAb61VXNom9XJau3/0EHumCAqrZVJQBuncXV3MouXCg0Yh9kSWMuVAhpn7YCf1xxe4KbmvvqIWqVBa2fzJXWzKDl3yWzVUiQAHqvGanBGzxzfn1Lg+7ZrmEbYKFQoGnJninlaBkuULkBnzpQDvuEQmGpYV239NGqeaqFLKF0ydjgP/Epb31ewppfQdtN9tLKgrcM3VVT7A0izscWFHOP9v6IELlyCs7BRepi3dDjC137rdzuCTcW9/582KgdMHI5Wfgpic7P3HXyhrhss+irzNy2/e58rnQHi0Z8FJ2JcRFHqjB6O/Aj8zASX1PXcTHbALFqTpAvwjxPctE+V0KeBukchrvxv+NkOj2/mFFA4q+fZYUVYgkanRyFn1LCE97rdd/zwy79OlDcxCIVLzejNlh+IA3z3fjIDEmMbfcFleBZTX7rULqJzmdQeI3lFfUUNbCg8+uk8ZnGtucPRmc8d7bxsNITJvEvixfP3z3q+ulg5/AeSctGB+EXFPRkZ9e4v", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [rerepresentatives = _t, block = _t, datetime = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"rerepresentatives", type text}, {"block", type text}, {"datetime", type datetime}}),
    #"Grouped Rows 1" = Table.Group(#"Changed Type", {"rerepresentatives", "block"}, {{"Count", each Table.RowCount(_), Int64.Type}}),
    #"Grouped Rows 2" = Table.Group(#"Grouped Rows 1", {"block"}, {{"AllRows", each SubGroupAddIndex(_), type table [block=nullable text, rerepresentatives=text, Count=number, Index=number]}}),
    SubGroupAddIndex = (Table as table) as table =>
    let
        #"Sorted Rows" = Table.Sort(Table,{{"Count", Order.Descending}}),
        #"Added Index" = Table.AddIndexColumn(#"Sorted Rows", "Index", 1, 1, Int64.Type)
    in         
        #"Added Index",
    #"Expanded AllRows" = Table.ExpandTableColumn(#"Grouped Rows 2", "AllRows", {"rerepresentatives", "Count", "Index"}, {"rerepresentatives", "Count", "IndexDense"}),
    #"Grouped Rows 3" = Table.Group(#"Expanded AllRows", {"block", "Count"}, {{"Index", each List.Min([IndexDense]), type nullable number}}),
    #"Merged Queries" = Table.NestedJoin(#"Expanded AllRows", {"block", "Count"}, #"Grouped Rows 3", {"block", "Count"}, "Grouped Rows", JoinKind.LeftOuter),
    #"Expanded Grouped Rows" = Table.ExpandTableColumn(#"Merged Queries", "Grouped Rows", {"Index"}, {"Grouped Rows.Index"}),
    #"Removed Columns" = Table.RemoveColumns(#"Expanded Grouped Rows",{"IndexDense"})
in
    #"Removed Columns"

Let me know if this helps 🙂

/Tom
https://www.tackytech.blog/
https://www.instagram.com/tackytechtom/