Create row sequence based on row id column but retaining other column

Hi Anonymous , After the index did you expand it Could you try this please 

• Sort the Data:

  • Sort the table first by the Row_Id column in ascending order, then by Split_Desc in ascending order. This ensures a proper order for generating the sequence.

• Group the Data:

  • Group the data by Row_Id:
    • Go to Transform → Group By.
    • Group by Row_Id.
    • Create an All Rows operation to retain the grouped table.

• Add an Index for Sequencing:

  • Within each group, add an index to generate the Row_Sequence:
    • After grouping, click the small table icon for the grouped column.
    • Add an Index Column starting from 1.
    • Rename this index column as Row_Sequence.

• Expand the Table:

  • After adding the index, expand the grouped table to flatten it back into a single table:
    • Click on the expand icon next to the grouped column.
    • Include all original columns and the newly created Row_Sequence column.

let
  
    Source = Table.FromRows(
        {
        //Data
        },
        {"Row_Id", "Split_Desc"}
    ),
    
    // Convert data types
    ChangeTypes = Table.TransformColumnTypes(Source, {{"Row_Id", Int64.Type}, {"Split_Desc", Text.Type}}),
    
    // Sort by Row_Id and Split_Desc
    SortedTable = Table.Sort(ChangeTypes, {{"Row_Id", Order.Ascending}, {"Split_Desc", Order.Ascending}}),
    
    // Group by Row_Id
    GroupedTable = Table.Group(
        SortedTable,
        {"Row_Id"},
        {
            {"AllData", each Table.AddIndexColumn(_, "Row_Sequence", 1, 1, Int64.Type)}
        }
    ),
    
    // Expand grouped data
    ExpandedTable = Table.ExpandTableColumn(GroupedTable, "AllData", {"Row_Id", "Split_Desc", "Row_Sequence"})
in
    ExpandedTable
​

If this post helped please do give a kudos and accept this as a solution
Thanks In Advance

Hi Akash_Varuna ,

Thanks for your quick response.

I have done exact steps that you mentioned. However with your step 'Group Data', you mentioned only one column 'row_id' hence it will group and after adding index, it will produce an accurate row sequencing. However, in this process, we are missing out on the 'split_desc' column which is what I am having trouble with!

I hope you get my point.

Hi,

Use the following formula in the advanced editor, or see the attached file

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUXJ3VorVgTIjwEwjIBPMMIYxTGAMUxjDDMgIC0Iww8BMc5i0BZBRUQFmWsLEDA2ArKoqpdhYAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Row_ID = _t, Split_Desc = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Row_ID", Int64.Type}, {"Split_Desc", type text}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"Row_ID"}, {{"Count", each Table.AddIndexColumn(_,"Row_Sequence",1)}}),
    #"Removed Columns" = Table.RemoveColumns(#"Grouped Rows",{"Row_ID"}),
    #"Expanded Count" = Table.ExpandTableColumn(#"Removed Columns", "Count", {"Row_ID", "Split_Desc", "Row_Sequence"}, {"Row_ID", "Split_Desc", "Row_Sequence"})
in
    #"Expanded Count"

Hi Anonymous, another 2 solutions here:

Output

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUXJ3VorVgTIjwEwjIBPMMIYxTGAMUxjDDMgIC0Iww8BMc5i0BZBRUQFmWsLEDA2ArKoqpdhYAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Row_ID = _t, Split_Desc = _t]),
    ChangedType = Table.TransformColumnTypes(Source,{{"Row_ID", Int64.Type}, {"Split_Desc", type text}}),
    v1_TableGroup = Table.Combine(Table.Group(ChangedType, {"Row_ID"}, {{"T", each Table.AddIndexColumn(_, "Row Sequence", 1, 1, Int64.Type), type table}})[T]),
    v2_ListGenerate = [ L = List.Buffer(v1_TableGroup[Row_ID]),
    LG = List.Generate(
                ()=> [x=0, y=L{x}, z=1],
                each [x] < List.Count(L),
                each [x=[x]+1, y=L{x}, z={1, [z]+1}{Byte.From(y=[y])} ],
                each [z] ),
    T = Table.FromColumns(Table.ToColumns(ChangedType) & {LG}, Value.Type(Table.FirstN(ChangedType, 0) & #table(type table[Row Sequence=Int64.Type], {})))
  ][T]
in
    v2_ListGenerate