locked
Counting Instances in a row across multiple columns RRS feed

  • Question

  • Hello,

    Using M language, I'd like to create a custom column in Power Query. This column will be called "Documentation - PF".

    In each row, I would like to count the number of times "Proactive Find" is found across a number of columns (ie: Column1, Column2, Column3, Column4) in said row.

    Currently I can get it so if any column contains "Proactive Find", the result is "Proactive Find". I need it to count the number of instances instead.

    Thank you!



    • Edited by patri0t82 Wednesday, April 10, 2019 3:13 PM
    Wednesday, April 10, 2019 3:12 PM

Answers

  • No problem :-)

     #"Added Custom" = Table.AddColumn(YourPreviousStep, "Documentation - PF", each List.Sum(List.Transform(Record.ToList(Record.SelectFields(_,{"Column1","Column2","Column3","Column5","Column7"})),each if _ = "Proactive find" then 1 else 0))      )
    where {"Column1","Column2","Column3","Column5","Column7"} is a list of columns in which you want to search
    • Marked as answer by patri0t82 Wednesday, April 10, 2019 4:39 PM
    Wednesday, April 10, 2019 4:30 PM

All replies

  • try

     #"Added Custom" = Table.AddColumn(YourPreviousStep, "Documentation - PF", each List.Sum(List.Transform(Record.ToList(_),each if _ = "Proactive find" then 1 else 0)), Int64.Type      )
    

    Wednesday, April 10, 2019 3:59 PM
  • Thank you for the suggestion. I should clarify, I've probably got 300 columns and in this "Documentation - PF" column I only want to represent about 25 of them. Is there a way using your formula to identify which columns to look in?

    Looking at your example, (which I've got to work across all columns), I expect it would look something like this (which obv. doesn't work!)

    #"Added Custom" = Table.AddColumn(YourPreviousStep, "Documentation - PF", each List.Sum(List.Transform(Record.ToList(_),each if ([Column1], [Column2], [Column3]) = "Proactive find" then 1 else 0)), Int64.Type      )

    • Edited by patri0t82 Wednesday, April 10, 2019 4:28 PM
    Wednesday, April 10, 2019 4:05 PM
  • No problem :-)

     #"Added Custom" = Table.AddColumn(YourPreviousStep, "Documentation - PF", each List.Sum(List.Transform(Record.ToList(Record.SelectFields(_,{"Column1","Column2","Column3","Column5","Column7"})),each if _ = "Proactive find" then 1 else 0))      )
    where {"Column1","Column2","Column3","Column5","Column7"} is a list of columns in which you want to search
    • Marked as answer by patri0t82 Wednesday, April 10, 2019 4:39 PM
    Wednesday, April 10, 2019 4:30 PM
  • Thank you so much! That's fantastic!
    Wednesday, April 10, 2019 4:39 PM