Forum Discussion
Generate column with count of values
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
6 Replies
=DIVIDE(SUM(Table[Volume]),CALCULATE(SUM(Table[Volume]),ALLEXCEPT(Table,Table[Product Code])))
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
Right Click on the Column(s) You want to group by --> Group By --> Operation --> Count Rows
=Table.Combine(Table.Group(YourTableName,"Product Code",{"n",each let a=Table.RowCount(_) in Table.AddColumn(_,"Custom",each [Value]/a)})[n])
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. 🙂
You could always reference the table, group-by to get the count, and merge it back in.
