Forum Discussion
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.
Thank you
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
1 Reply
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