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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

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 2024 Power BI Update

Power BI Monthly Update - July 2024

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

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

Top Solution Authors
Top Kudoed Authors