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 1234 1 1234 2 1234 3 1234 ...
Need Help
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

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    Sorry, for late reaction. Was to busy with other topics to work on with this in the last days. Tried now your first step in the advanced editor. As its in german I had to change "source" to "quelle". But it says it cant find the column "Invoice number". I have checked it several times. Its written correctly. 

     

     

    I guess it has something to do with the way how the Data is imported:

    Its expanding the table just after the second step:

    What do I have to write instead of "source".

    Sorry, but i am a complete newbie here.

     

    Thx in advance

     

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

      Hi Anonymous ,

       

      It looks like you need to promote your table headers first. Go to the Transform tab > 'Use First Row as Headers'.

      Once you've done that, just change "Invoice no." in my code to "Invoice number" to match your actual column name.

       

      Pete

      • Anonymous's avatar
        Anonymous
        Not applicable
        4 years ago

        Hi Pete,

         

        both steps you advise were actually already done, just not visible on my screenshots:

         

        Here the full formula line I have added to advanced editor:

         

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

         

        But this is still the outcome: