Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago

Numbering Rows - Restart row count at each "new" value

Hi all,

 

I have data in column A and I need to number it, but I need the numbering to restart when there is a new value. See example below:

 

Column A 
ABC1
ABC2
ABC3
CDB1
CDB2
DCB1
ADB1
ADB2

 

My Column A data is coming from an ODBC database so it needs to stay connected so my data will refresh. Let me know if this doesn't make sense and I can try explaining it a different way.


Thanks!

Need Help

2 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    Hi Anonymous ,

    I'd like to suggest you enter to query editor and use 'add index column' feature:

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WcnRyVorVwaSdXZxQaBdnCO3ogkTHAgA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [#"Column A" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column A", type text}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"Column A"}, {{"Index", each Table.AddIndexColumn(_,"Index",1,1)[Index], type list}}),
    #"Expanded Count" = Table.ExpandListColumn(#"Grouped Rows", "Index")
in
    #"Expanded Count"

    Notice: these index numbers will auto increased every time your records increased.
    Regards,

    Xiaoxin Sheng