Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
2 years ago
Solved

COUNTIFS in Power Query

Hi all!   I've tried to find it before posting as a new topic but I couldn't find anything.   I'm trying to count how many "Y" I have on an specific range in the same row - which in excel I'd use...
Need Help
  • Anonymous's avatar
    Anonymous
    2 years ago

    In case anyone is interested, this is what I tried and it worked:

     

    #"Added Count of Ys" = Table.AddColumn(#"Added Payment Type", "Count of Ys", each List.Count (List.Select({34,35,36,37}, each _ = "Y")))

     

    Where: 34,35,36 and 37 are the column numbers in Power Query.

     

    I've noticed that the created column is actually named as "count" only and comes right after column 37 instead - which I'm happy with that but just so you bear in mind in case it looks like it works but you don't see the column towards the end of your data as expected.

     

    Thanks,

    Ana

Ahmedx's avatar
Ahmedx
Super User
2 years ago

pls try this

List.Count(
 List.Select(
  Record.FieldValues(_), 
                each _ = "Y")
                )

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    Hi Ahmedx,

     

    Thanks for that. This is what I tried last week but it counts all "Y"s from my row - my data has more than those four columns so I need to specify which columns it needs to read from.

     

    Thanks,

    Ana

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    In case anyone is interested, this is what I tried and it worked:

     

    #"Added Count of Ys" = Table.AddColumn(#"Added Payment Type", "Count of Ys", each List.Count (List.Select({34,35,36,37}, each _ = "Y")))

     

    Where: 34,35,36 and 37 are the column numbers in Power Query.

     

    I've noticed that the created column is actually named as "count" only and comes right after column 37 instead - which I'm happy with that but just so you bear in mind in case it looks like it works but you don't see the column towards the end of your data as expected.

     

    Thanks,

    Ana

    • vkboddapti's avatar
      vkboddapti
      Regular Visitor
      2 years ago

      Dear Anonymous Doesn't this work if you give reference of the actual column names instead of column numbers? 
      Because, when I have used below code, I'm getting 0 as result in all of the rows, I wonder what is the issue in this:

      = Table.AddColumn(#"Changed Type", "TotalMinsPerWeek", each List.Count(List.Select({"Monday","Tuesday","Wednesday","Thursday","Friday","Saturday","Sunday"}, each _ = "Y")))