Generate column with count of values

[naveen73]

Hi all,

I am working with a dataset and merging another set and applying filters.

At some point I am getting the “Value” column (see screenshot). However, the values are a sum of each of the “Product Code”. This mean I need to have a column so that I it shows the number of instances the “Product Code” appears i.e. “Generate Column”.  Then I can make a column to the right of it: “My Column”.

Can you help me with generating “Generate Column”, please?

Strangely enough I am not able to find the answer to this question not on the Internet.

Thanks,

Naveen

[j_ocean]

You could always reference the table, group-by to get the count, and merge it back in.

[hmonty]

Hi, I also struggled to find any help online with this issue.
My approch has been (in power query), to select the column I want to count, and go to group by, click on the advanced tab and give the new column a name, select count rows, then add another column underneath and select all rows. 

when you click ok, you should get the column you wanted to group by, a count of the values and a column with a table you can expand
expanding return your orginal columns, plus the count of selected values in a new column.

I hope this helps with your query. 🙂

[naveen73]

Hi @wdx223_Daniel 

Thanks for your efforts. I was looking for a solution in M language. Would you (anybody) be able to help me with in M. Perhaps it is technically not possible in M?

Thanks,

Naveen

[wdx223_Daniel]

=Table.Combine(Table.Group(YourTableName,"Product Code",{"n",each let a=Table.RowCount(_) in Table.AddColumn(_,"Custom",each [Value]/a)})[n])

[spinfuzer]

Right Click on the Column(s) You want to group by --> Group By --> Operation --> Count Rows

[wdx223_Daniel]

=DIVIDE(SUM(Table[Volume]),CALCULATE(SUM(Table[Volume]),ALLEXCEPT(Table,Table[Product Code])))