Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

Reply
naveen73
Helper III
Helper III

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.

 

naveen73_0-1700436470104.png

 

Thanks,

 

Naveen

6 REPLIES 6
j_ocean
Helper V
Helper V

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

hmonty
Frequent Visitor

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. 

1.jpg

 

 

 

 

 

 

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 expand2.jpg
expanding return your orginal columns, plus the count of selected values in a new column.

 

I hope this helps with your query. 🙂

naveen73
Helper III
Helper III

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

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

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

wdx223_Daniel
Super User
Super User

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

Helpful resources

Announcements
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.

Power BI Header Change

Power BI Header Update

Read about the exciting updates for the Power BI forum.

Top Solution Authors
Top Kudoed Authors