Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago

How to add a column which counts up the repeating value in another column?

Hi,

 

i would like to add a column in my dataset which counts up the repeating invoice numbers. Result should look like this: 

 

Invoice no.Count up
12341
12342
12343
12344
12345
23451
23452
23453
34561
34562
34563
34564
……

 

Thank you in advance for your support.

Need Help

31 Replies

  • Vijay_A_Verma's avatar
    Vijay_A_Verma
    Icon for Most Valuable Professional rankMost Valuable Professional
    4 years ago

    Solution file upload to - https://1drv.ms/x/s!Akd5y6ruJhvhuSx85gpq2K8FOskP?e=KNg8N5

    Use below code

     

    let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Invoice no.", Int64.Type}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"Invoice no."}, {{"Count", each Table.RowCount(_), Int64.Type}}),
    #"Added Custom" = Table.AddColumn(#"Grouped Rows", "Count up", each {1..[Count]}),
    #"Expanded Count up" = Table.ExpandListColumn(#"Added Custom", "Count up"),
    #"Removed Columns" = Table.RemoveColumns(#"Expanded Count up",{"Count"})
in
    #"Removed Columns"

     

     

  • BA_Pete's avatar
    BA_Pete
    Icon for Super User rankSuper User
    4 years ago

    Hi Anonymous ,

     

    Paste this into a new blank query using Advanced Editor. It gives you both a total count and running count:

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjQyNlHSUTJUitWBc4yQOcbIHBNkjimYA2Sbwg2AcoyQORADgGwzuDIoxwiZg6IMaE8sAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Invoice no." = _t, #"Count up" = _t]),
    groupInvNo = Table.Group(Source, {"Invoice no."}, {{"Count", each Table.RowCount(_), Int64.Type}, {"data", each _, type table [#"Invoice no."=nullable text, Count up=nullable text]}}),
    addNestedIndex = Table.TransformColumns(groupInvNo, {"data", each Table.AddIndexColumn(_, "Index", 1, 1)}),
    expandIndex = Table.ExpandTableColumn(addNestedIndex, "data", {"Index"}, {"Index"})
in
    expandIndex

     

     

     

    Pete

    • Anonymous's avatar
      Anonymous
      Not applicable
      4 years ago

      Hi Pete,

       

      thank you for the quick response. I think thats generally the right thing, but using your skript its not adding the colums to my existing query. Whats the formula to just add the column "Index" to my existing data? I cant figure it out.

       

       

      • BA_Pete's avatar
        BA_Pete
        Icon for Super User rankSuper User
        4 years ago

        Hi Anonymous ,

         

        It's not a formula itself, you need to group your data on [Invoice No] first, with an All Rows aggregation column, add the index, then expand the index back out again.

        It's not something you can do with just a calculated column as Power Query has no concept of the separate invoice numbers until you group on them.

         

        This step creates the group with an All Rows aggregated column (called [data]) and a count column ([Count]):

        groupInvNo = Table.Group(Source, {"Invoice no."}, {{"Count", each Table.RowCount(_), Int64.Type}, {"data", each _, type table [#"Invoice no."=nullable text, Count up=nullable text]}}),

         

        Then, this step adds an index column ([Index]) to the nested tables within the All Rows group column:

        addNestedIndex = Table.TransformColumns(groupInvNo, {"data", each Table.AddIndexColumn(_, "Index", 1, 1)}),

         

        Finally, this step expands the index ([Index]) back out to reinstate all your original rows:

        expandIndex = Table.ExpandTableColumn(addNestedIndex, "data", {"Index"}, {"Index"})

         

        Pete