Creating an index column based on the first instance of two columns

Hi cats_oi ,

Before:

After:

Here the code in Power Query M that you can paste into the advanced editor (if you do not know, how to exactly do this, please check out this quick walkthrough)

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("jdK7CsMwDAXQf/EcsB4udC3t0rVryOCQUEqh6eD/pzYhkNRSokUgOFy4Qm3r0DXuGlOeCEBlY/RInoA4L/dPiu/RdY0iAYsMR5LOHsgkSyYv8jJM33HQJHgINpkzT4KkvN6m5yzDXqNabhodZkqNBKk0kjOlRryfuWokyHXmn3zEvn8lAw7mdxKlePxaau8kZ7JNKoeSM6vjdz8=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [ID = _t, Type = _t, Zip = _t, Date = _t, Status = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"ID", Int64.Type}, {"Type", type text}, {"Zip", Int64.Type}, {"Date", type date}, {"Status", type text}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"ID", "Status"}, {{"FirstDate", each List.Min([Date]), type nullable date}}),
    #"Merged Queries" = Table.NestedJoin(#"Changed Type", {"ID", "Status", "Date"}, #"Grouped Rows", {"ID", "Status", "FirstDate"}, "Added Custom", JoinKind.LeftOuter),
    #"Expanded Added Custom" = Table.ExpandTableColumn(#"Merged Queries", "Added Custom", {"FirstDate"}, {"Added Custom.FirstDate"}),
    #"Added Custom" = Table.AddColumn(#"Expanded Added Custom", "Custom", each if [Added Custom.FirstDate] is null then 0 else 1),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"Added Custom.FirstDate"}),
    #"Sorted Rows" = Table.Sort(#"Removed Columns",{{"ID", Order.Ascending}, {"Status", Order.Descending}, {"Date", Order.Ascending}})
in
    #"Sorted Rows"

I only grouped by ID and Status (and not type). which is why rabbit has a 0 instead of a 1. If you neeed a 1 there, too, just add Type to the Group By and Join condition.

Let me know if this helps 🙂

/Tom
https://www.tackytech.blog/
https://www.instagram.com/tackytechtom/

let
    Source = your_table,
    rows = List.Buffer(Table.ToRecords(Source)), 
    gen = List.Generate(
        () => [i = 0, r = rows{0}, desired = 1],
        (x) => x[i] < List.Count(rows), 
        (x) => [i = x[i] + 1, r = rows{i}, desired = if r[[ID], [Status]] = x[r][[ID], [Status]] then 0 else 1],
        (x) => x[r] & [Desired_Column = x[desired]]
    ),
    tbl = Table.FromRecords(gen)
in
    tbl