I have a question about the Power Querym function.

[Minseok]

Hello, everyone.

I have a question about how to use PowerQuery functions.

I want to give a rank as shown below.

How should I configure the function?

[Anonymous · Accepted solution]

Hi @Minseok ,

Please put all of this M function into Advanced Editor:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMtQ30jcyMDJW0lHKLc7OzFWK1YlWMtU3xhQEqoMrRVJogC5mCFRljC5orm9oBhfLBQnGAgA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [reg_date = _t, member_id = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"reg_date", type date}, {"member_id", type text}}),
    #"GroupedRows" = Table.Group(#"Changed Type", {"member_id"}, {{"AllData", each _ ,type table}}),
    #"SortedIndexedRows" = Table.TransformColumns(#"GroupedRows", {"AllData", (table) =>
          let 
              SortedTable = Table.Sort(table, {{"reg_date", Order.Descending}}),
              IndexedTable = 
                  Table.AddIndexColumn(SortedTable, "RankOrder", 1, 1, Int64.Type)
                  in 
                      IndexedTable
            }),
    #"ExpandedTable" = 
    Table.ExpandTableColumn(#"SortedIndexedRows", "AllData", {"reg_date", "member_id", "RankOrder"}, {"reg_date", "member_id1", "RankOrder"}),
    #"Removed Columns" = Table.RemoveColumns(ExpandedTable,{"member_id1"})
in
    #"Removed Columns"

The final output is as follow:

But if you need to implement sorting different ids separately, I suggest it might be more convenient to use DAX.

Best Regards,
Dino Tao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[mussaenda]

How is the logic behind the Rank?

the 3rd row member  on the right table does not have any same name but it ranks together with kim member