Create a column with entries based on one with ID and another with sequence

[Tamillllll]

I want to create a new column as 'Type' based on two columns with one column being an ID and the other being a sequence of numbers. For each ID, the starting number should be entered as the First type, the following numbers as Intermediate, and the last number should be entered as the Last type. below is the sample table, the original table will have many unique IDs with this sequence. Can you please help me with the steps/ideas on how this can be achieved? Any help is appreciated.

[dufoq3]

Hi @Tamillllll, different approach here.

Result

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WclTSUTJUitWBsIzgLGM4ywTOMgWznOA6nOA6nOA6nOA6nFB0mMFZ5kqxsQA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Id = _t, #"Stop Sequence" = _t]),
    // Added [Type] to intermediate [All] table
    GroupedRows = Table.Group(Source, {"Id"}, {{"All", each 
        Table.AddColumn(_, "Type", (x)=>
              if x[Stop Sequence] = List.Min([Stop Sequence]) then "First" else
              if x[Stop Sequence] = List.Max([Stop Sequence]) then "Last"
              else "Intermediate", type text), type table}} ),
    CombinedAll = Table.Combine(GroupedRows[All])
in
    CombinedAll

[Tamillllll]

Hi @AlienSx 

Thank you so much for the solution, sorry if my table was confusing, its based on two columns grouping Id's together and from the stop sequence column - taking 1 as 'first' and last highest as 'Last' and intermediate numbers as 'Intermediate'. Let me know if below helps.

[AlienSx]

@Tamillllll then sort by sequence column first. If you want to retain original sorting order then add index column before that and sort by this column in the end.

let
    Source = your_table,
    f = (tbl) =>
        [a = Table.Sort(tbl, "Step Sequence"),
        recs = List.Buffer(Table.ToRecords(a)),
        count = List.Count(recs),
        gen = List.Generate(
            () => [i = 0, r = recs{0} & [Type = "First"]],
            (x) => x[i] < count,
            (x) => [i = x[i] + 1, r = recs{i} & [Type = if i + 1 = count then "Last" else "Intermediate"]],
            (x) => x[r]
        )][gen],
    g = Table.Group(Source, {"id"}, {{"a", f}}),
    exp = Table.FromRecords(List.Combine(g[a]))
in
    exp

[AlienSx]

@Tamillllll 

let
    Source = your_table,
    f = (tbl) =>
        [recs = List.Buffer(Table.ToRecords(tbl)),
        count = List.Count(recs),
        gen = List.Generate(
            () => [i = 0, r = recs{0} & [Type = "First"]],
            (x) => x[i] < count,
            (x) => [i = x[i] + 1, r = recs{i} & [Type = if i + 1 = count then "Last" else "Intermediate"]],
            (x) => x[r]
        )][gen],
    g = Table.Group(Source, {"id"}, {{"a", f}}),
    exp = Table.FromRecords(List.Combine(g[a]))
in
    exp