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

DOLEARY85's avatar
DOLEARY85
Resident Rockstar
3 years ago

Hi,

 

you could try using this in a custom column:

 

let thisrecordlist = Record.ToList(_) in List.Count(List.Select(List.Transform({2,3}, each thisrecordlist{_}), each Text.Contains(_, "Y")))

 

you'll need to change the numbers of the columns you want to include in the section List.Transform({2,3} this list would pick up the equivilant of column B and C in excel.

 

If I answered your question, please mark my post as solution, Appreciate your Kudos 👍

  • Anonymous's avatar
    Anonymous
    Not applicable
    3 years ago

    Hello,

     

    Thanks for that. That hasn't worked but I've tried something similar which it worked:

    = Table.AddColumn(

    #"Added Payment Type", "Count of Ys", each

    List.Count (

    List.Select({34,35,36,37}, each _ = "Y")))

     

     

    Thanks,

    Ana