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

     

     

Omid_Motamedise's avatar
Omid_Motamedise
Icon for Super User rankSuper User
1 year ago

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"