SORT BY MULTIPLE CONDITIONS

[ss89]

Hello Community,

I need to calculate SORT BY Column grouped by each LOT_NUMBER Column and sorted in an ascending STAGE SEQUENCE Column.

SORT BY Column should be like this:

LOT_NUMBER	STAGE SEQUENCE	SORT BY
0019	6	1
0019	7	2
0019	4	3
0019	2	4
0019	5	5
0019	3	6
0019	1	7
0020	5	1
0020	6	2
0020	3	3
0020	2	4
0020	4	5
0020	1	6

Thanks in advance for your support.

[v-rzhou-msft · Accepted solution]

Hi @ss89 ,

Here I suggest you to add an index column grouped by LOT_NUMBER column in Power Query Editor.

Copy the code as below and paste it into Advanced Editor in Power Query Editor.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("TcyxCQAgDADBXVJbxKgRZwnZfw1FRL48eD5CVOuSIi5ZPibRCSMG0Yj6YIrswolGGNGJc8sN", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [LOT_NUMBER = _t, #"STAGE SEQUENCE" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"LOT_NUMBER", Int64.Type}, {"STAGE SEQUENCE", Int64.Type}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"LOT_NUMBER"}, {{"Count", each _, type table [LOT_NUMBER=nullable number, STAGE SEQUENCE=nullable number]}}),
    #"Added Custom" = Table.AddColumn(#"Grouped Rows", "Custom", each Table.AddIndexColumn([Count],"Index",1)),
    #"Expanded Custom" = Table.ExpandTableColumn(#"Added Custom", "Custom", {"STAGE SEQUENCE", "Index"}, {"Custom.STAGE SEQUENCE", "Custom.Index"}),
    #"Removed Columns" = Table.RemoveColumns(#"Expanded Custom",{"Count"}),
    #"Renamed Columns" = Table.RenameColumns(#"Removed Columns",{{"Custom.STAGE SEQUENCE", "STAGE SEQUENCE"}, {"Custom.Index", "Index"}})
in
    #"Renamed Columns"

New Table:

For reference:Create Row Number for Each Group in Power BI using Power Query

Best Regards,
Rico Zhou

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