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

[Brij]

Hi,

I really need help with row sequencing based on a column (row_id) while retaining another column called 'split_desc'.

There are two columns in the table - 'row_id' and 'split_desc' and they look like the below sample:

I have tried using group by on row_id and then adding index will give me perfect result that I want but in that case I lose splic_desc column which is vital for me to keep in this table. When I include row_id and split_desc both in the group by and then add index it does not giving me the correct result as group by will consider both the column and grouped them and index will sequece accordingly which is what I don't want.

Currenlty I'm getting the below result, which is not correct, row_sequence highlighted in yellow should produce index # 2 but it is giving me #1 when adding both column in group by statement! 

A new row_sequence column should be purly based on row_id column while retaining the split_desc column. Here is the desire result that I am trying to achieve.

 Any help is appreicated.

Pat

[dufoq3]

Hi @Brij, 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

[Omid_Motamedise]

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"

[Brij]

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.

[SundarRaj · Accepted solution]

Hi @Brij. Here's a solution you could take a look at! Thanks

[Brij]

Hi @SundarRaj ,

This is perfect solution. It works for me.

Not sure what mistake I was doing? Could you please explain the last step (Soln) that you created. I was trying to get this step done through UI and was not getting the desire result.

Appreciate if you can explain.

Thank you

Brijesh

[SundarRaj]

Sure @Brij
1. Consider the second image. That Table shows you the step before the Indexing.

2. Table.TransformColumns helps me to get into a table, specify the column (the "All" column) on which I'd like to make alterations (in this case, add the index column).

3. After the indexing, what I'd like to do is combine all the tables and get the desired result. Table.Combine accepts Tables as list and combines them ( [ColumnName] gives me the Tables in a list format, just what Table.Combine accepts ).

If you still need clarifications, don't hesitate to ping back. Thanks!

[Brij]

Thank you @SundarRaj 

[Akash_Varuna]

Hi @Brij , 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