Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 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
    3 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
3 years ago

pls try this

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

Anonymous's avatar
Anonymous
Not applicable
3 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