Add a list ranked by category using M Query.

[roxy12]

In the data, there are representatives for each block, and the same person appears multiple times depending on the date and time. I'd like to create a column that groups by block and representative, counting the number of representatives for each block and ranking them.

I will provide the source that was entered in "Data Input," so please feel free to use it if you like.

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])

This is a display of the final data that has been processed in Excel.

If anyone knows of any good ideas or methods to achieve a final result like this, could you please share them with me?

Best regards,

roxy

[CNENFRNL]

Genearlly speaking, PQ was designed for data cleansing instead of calculation. DAX solution is way more elegant.

[tackytechtom · Accepted solution]

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/

[roxy12]

@tackytechtom 

Thank you very much for your advice!!