Forum Discussion

Learner1585's avatar
Learner1585
Icon for Helper I rankHelper I
2 years ago
Solved

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

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...
Need Help
Show and Tell
Tips and Tricks
  • Learner1585's avatar
    Learner1585
    2 years ago

    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

ImkeF's avatar
ImkeF
Icon for Community Champion rankCommunity Champion
2 years ago

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's avatar
Learner1585
Icon for Helper I rankHelper I
2 years ago

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