Forum Discussion

Tamillllll's avatar
Tamillllll
Frequent Visitor
2 years ago

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

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 typ...
Need Help
Tamillllll's avatar
Tamillllll
Frequent Visitor
2 years ago

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's avatar
    AlienSx
    Icon for Super User rankSuper User
    2 years ago

    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