Advanced Sort Table

[credencial]

Hi folks,

I need help.

I need to sort each dotted rectangle as ascending by the "DATA" column for the same value columns: "RAZÃO", "COD_FORNE", "REFER_PRODUTO" and add a new INDICE (INDEX) column like this:

Its possible in M (preferably) or DAX?

Thanks for any help.

[v-jingzhang · Accepted solution]

Hi @credencial 

Create a blank query and open its Advanced Editor. Then paste below codes to replace all codes in Advanced editor to see the steps.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjIwMjAwVtJRcgRiCzMQK9DQSClWByplglvKFL8uJ7iUpaUlQsoMt5Q5bimYXcZmpuh2WeCWssQtZYIpFQsA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [DATA = _t, CODE = _t, REFER = _t, NAME = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"DATA", Int64.Type}, {"CODE", type text}, {"REFER", Int64.Type}, {"NAME", type text}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"CODE", "REFER", "NAME"}, {{"All", each _, type table [DATA=nullable number, CODE=nullable text, REFER=nullable number, NAME=nullable text]}}),
    #"Added Custom" = Table.AddColumn(#"Grouped Rows", "Custom", each Table.AddIndexColumn([All], "Index", 0, 1, Int64.Type)),
    #"Removed Other Columns" = Table.SelectColumns(#"Added Custom",{"Custom"}),
    #"Expanded Custom" = Table.ExpandTableColumn(#"Removed Other Columns", "Custom", {"DATA", "CODE", "REFER", "NAME", "Index"}, {"DATA", "CODE", "REFER", "NAME", "Index"})
in
    #"Expanded Custom"

Attach pbix file for your reference.

Regards,
Community Support Team _ Jing
If this post helps, please Accept it as the solution to help other members find it.

[CNENFRNL]

Easy job with PQ. First, group the table by the 3 columns; then add index to each grouped table.

[credencial]

Tanks for your reply @CNENFRNL.

Sorry, I couldn't see your solution. Can be more specific?

The columns are already grouped, they should only be sorted by the "DATE" column, but in sections as shown in the dotted line.