undefinedCrew another custom column to count how many times each row value occurs in power query

[Learner1585]

Hi,

I want to create a custom column to count how many times a specic row value occurs in a column.

I dont want to create a calculated column but a custom colum as a transformation step in power query only.

Below is the example of what i am looking for.

1.  There is a column name UID
2. I want to create another custom column where each row shows how many times the adjacent value occurs.

Below is how I would do this in excel.

thank you very much

[ImkeF]

Yes, exactly.
So the end of your code would look like so:

  #"Added Index" = Table.AddIndexColumn(#"Merged Columns1", "Index", 1, 1, Int64.Type),
BufferedList = List.Buffer(  #"Added Index" [4sector UID]),
AddColumn = Table.AddColumn(  #"Added Index" , "Count", each List.Count(List.Select(BufferedList, (l)=> l=[4sector UID])))
in
AddColumn

[Learner1585]

thank you @ImkeF 

[ImkeF]

Hi @Learner1585 ,
you can do it like so:

PreviousStepName = <Your existing code here>,
BufferedList = List.Buffer(PreviousStepName[4sector UID]),
AddColumn = Table.AddColumn(PreviousStepName, "Count", each List.Count(List.Select(BufferedList, (l)=> l=[4sector UID])))

For performance reasons, you are adding an intermediate step where you buffer the column that you want to search through in each row.
Then you add the column where you count the values that match the current row.

[Learner1585 · Accepted solution]

Hi,
Thank you very much for your kind reply.

Pardon me for my follow up, but being not so pro , can I seek your help again on how to add your codes into my existing codes.

Should I just add your code after my last step " 

    #"Added Index" = Table.AddIndexColumn(#"Merged Columns1", "Index", 1, 1, Int64.Type)

• PreviousStepName = <Your existing code here>,
  BufferedList = List.Buffer(PreviousStepName[4sector UID]),
  AddColumn = Table.AddColumn(PreviousStepName, "Count", each List.Count(List.Select(BufferedList, (l)=> l=[4sector UID])))

Thanks again