Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
1 year ago
Solved

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

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 ...
Need Help
  • SundarRaj's avatar
    SundarRaj
    1 year ago

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

     

     

dufoq3's avatar
dufoq3
Icon for Community Champion rankCommunity Champion
1 year ago

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