Custom index in M language

[Anonymous]

A dataset looks like the following. It has been sorted by TractorNumber (asc) and then StartDateTime (desc). I want to add an index ("Ranking (End Result)") to rank the Status of every TractorNumber. The statuses with consecutive repeats have the same rankings; for example, #1,  2, and 3. Because of the size of the dataset, I want to do it in M.

The table source code in M is after the screenshot. Can anyone help me with the M code for Ranking (End Result) please? Thank you.

The table source code:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("jZTLagMxDEV/xWSdEXr4ObvQZtVQSku7Cf3/36it8aSxkxkMsxgQhyvp6vp6PRASW3c4Hj5On2/n1/zzgoIR8w8j84T5E0NudjQTgTjJFTr8HjdQSxWlCeOEyWBU1IF9hp5+Lo9cmDgYTMp5iBJKpeW+v85POTGorYoDVE4G9PwkaDAUjhkilortRry8b3HLaiwIFT030KefeN0LRhDt0w/pcTQk2me2AgsXhuZjZ8iv+1Rz41if1hBWzvniXxrTs6t/AYLOl8tDgtlAO7usGcFphQaPLbNsiHSpBDao6MjVKIh5OzyTQFAXSYZF6X9SisreTkcC3lmJbO9TRWKYFWTghPfR6DjCjltOAF09uS3uQW+xEgVInnDrcnqOa5/55CI3UdzXI13qcuK2ieK+Hq592hqNrX1633HFCCkcpyaKu3rliQrFfEZgaqK4O1/hnHIMyTVR7PSC6zmu/jlsorjf5xp9ink+eeS2+oz1QNGD1QNNLbdGuOduTzBDsG2E9wVDfUspgVcjSoR//wA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [TractorNumber = _t, Status = _t, Terminal = _t, StartDateTime = _t, #"Ranking (End Result)" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"TractorNumber", Int64.Type}, {"Terminal", type text}, {"Status", type text}, {"StartDateTime", type datetime}}),
    #"Sorted Rows" = Table.Sort(#"Changed Type",{{"TractorNumber", Order.Ascending}, {"StartDateTime", Order.Descending}})
in
    #"Sorted Rows"

[HotChilli · Accepted solution]

I think the strategy here would be to Group By TractorNumber with all rows, then within each group use Groupkind.local to get the consecutive repeats.  By adding the Index at the right stage and expanding, it should work.

Here's the M code (and I wouldn't normally deliver everything but it is reasonably complex)

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("jZTLagMxDEV/xWSdEXr4ObvQZtVQSku7Cf3/36it8aSxkxkMsxgQhyvp6vp6PRASW3c4Hj5On2/n1/zzgoIR8w8j84T5E0NudjQTgTjJFTr8HjdQSxWlCeOEyWBU1IF9hp5+Lo9cmDgYTMp5iBJKpeW+v85POTGorYoDVE4G9PwkaDAUjhkilortRry8b3HLaiwIFT030KefeN0LRhDt0w/pcTQk2me2AgsXhuZjZ8iv+1Rz41if1hBWzvniXxrTs6t/AYLOl8tDgtlAO7usGcFphQaPLbNsiHSpBDao6MjVKIh5OzyTQFAXSYZF6X9SisreTkcC3lmJbO9TRWKYFWTghPfR6DjCjltOAF09uS3uQW+xEgVInnDrcnqOa5/55CI3UdzXI13qcuK2ieK+Hq592hqNrX1633HFCCkcpyaKu3rliQrFfEZgaqK4O1/hnHIMyTVR7PSC6zmu/jlsorjf5xp9ink+eeS2+oz1QNGD1QNNLbdGuOduTzBDsG2E9wVDfUspgVcjSoR//wA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [TractorNumber = _t, Status = _t, Terminal = _t, StartDateTime = _t, #"Ranking (End Result)" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"TractorNumber", Int64.Type}, {"Terminal", type text}, {"Status", type text}, {"StartDateTime", type datetime}}),
    #"Sorted Rows" = Table.Sort(#"Changed Type",{{"TractorNumber", Order.Ascending}, {"StartDateTime", Order.Descending}}),
    #"Removed Columns1" = Table.RemoveColumns(#"Sorted Rows",{"Ranking (End Result)"}),
    #"Grouped Rows" = Table.Group(#"Removed Columns1", {"TractorNumber"}, {{"2ndAll", each _, type table [TractorNumber=nullable number, Status=nullable text, all=table]}}),
    #"Added Custom" = Table.AddColumn(#"Grouped Rows", "Custom", each Table.Group(
                    [2ndAll]
                    , {"TractorNumber", "Status"}, {{"all", each _, type table [TractorNumber=nullable number, Status=nullable text, Terminal=nullable text, StartDateTime=nullable datetime]}}
                    , GroupKind.Local)),
    #"Added Index" = Table.AddColumn(#"Added Custom", "test", each Table.AddIndexColumn([Custom], "Index", 1, 1, Int64.Type)),
    #"Removed Columns" = Table.RemoveColumns(#"Added Index",{"2ndAll", "Custom"}),
    #"Expanded test" = Table.ExpandTableColumn(#"Removed Columns", "test", {"Status", "all", "Index"}, {"test.Status", "test.all", "test.Index"}),
    #"Expanded test.all" = Table.ExpandTableColumn(#"Expanded test", "test.all", {"Terminal", "StartDateTime"}, {"test.all.Terminal", "test.all.StartDateTime"})
in
    #"Expanded test.all"

Please test at your side and let me know how it goes.

[Anonymous]

Hi @Ukhotc, the solution works! Thank you VERY MUCH!!!