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 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.

 

 

Need Help

4 Replies

  • AlienSx's avatar
    AlienSx
    Icon for Super User rankSuper User
    2 years ago

    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
  • 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

       

  • dufoq3's avatar
    dufoq3
    Icon for Community Champion rankCommunity Champion
    2 years ago

    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