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

Enhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.

Reply
Ady23
New Member

Custom Column to count duplicate values as 1 , 2, and 3 (counting the occurence)

Hello guys, 

I am trying to add custom column in power query editor to show how many times the id  appears as shown below in excel .  I am only seeing DAX but not that much in power query.

 

Ady23_1-1716373258361.png

Thank you

1 ACCEPTED SOLUTION
m_dekorte
Super User
Super User

Hi @Ady23 

 

The process to achieve this with Power Query is as follows:

1. Group By on the ID column (using that as the key)

2. Change the aggregation to "All Rows" and the new column name to "temp" and click Okay

 

That will generate code that looks something like this:
Table.Group( YourTable, {"ID"}, {{"temp", each _, type table[...]}})

 

update this part: each _

into this: each Table.AddIndexColumn( _, "# of occurance", 1, 1)

Your choice remove the type table [...] OR include the new field type table [... , #"# of occurance"=number]

 

I hope this is helpful

View solution in original post

1 REPLY 1
m_dekorte
Super User
Super User

Hi @Ady23 

 

The process to achieve this with Power Query is as follows:

1. Group By on the ID column (using that as the key)

2. Change the aggregation to "All Rows" and the new column name to "temp" and click Okay

 

That will generate code that looks something like this:
Table.Group( YourTable, {"ID"}, {{"temp", each _, type table[...]}})

 

update this part: each _

into this: each Table.AddIndexColumn( _, "# of occurance", 1, 1)

Your choice remove the type table [...] OR include the new field type table [... , #"# of occurance"=number]

 

I hope this is helpful

Helpful resources

Announcements
July 2025 community update carousel

Fabric Community Update - July 2025

Find out what's new and trending in the Fabric community.

July PBI25 Carousel

Power BI Monthly Update - July 2025

Check out the July 2025 Power BI update to learn about new features.

Join our Fabric User Panel

Join our Fabric User Panel

This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.