Forum Discussion

Syndicate_Admin's avatar
Syndicate_Admin
Administrator
2 years ago

Add an Incremental Repeated Value Count column

Hello, in power query I have a column with ordered order numbers but that can be repeated for example with this data (222,333,333,333,555,555) and I want to add a column where I incrementally count the number of each order, that is, the result would be this from the Counting column, how do you do this in power Query by adding a calculated column?

ORDER IDCount
2221
3331
3332
3333
5551
5552

2 Replies

  • parry2k's avatar
    parry2k
    Super User
    2 years ago

    Syndicate_Admin start a new blank query click advanced editor and copy the following M code, it has all the steps to make this happen.

     

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjIyUorViVYyNjbGSpuamiLoWAA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", Int64.Type}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"Column1"}, {{"all", each Table.AddIndexColumn(_, "Index",1,1)}}),
    #"Expanded all" = Table.ExpandTableColumn(#"Grouped Rows", "all", {"Index"}, {"Index"})
in
    #"Expanded all"

     

     

    • GiaPa's avatar
      GiaPa
      Frequent Visitor
      2 years ago

      What if our original column is text and we want to count the number of time the text appears? Do we just change the int64 type?