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
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:

       

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

        Hi Anonymous ,

         

        *EDIT* You answered just at the same time I did! So it's not the first issue.

         

        Can you copy and paste your whole M code into a code window (</> button above) please?

        Once I've translated it from Germa, I should be able to add my code into it.

         

        *************************************************************************

         

        My guess is either that your column name has hidden characters e.g. "Invoice number    ", or you remove that column in a step prior to trying to group.

         

        Pete