COUNTIFS in Power Query

[AnaR]

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 =COUNTIFS(AI2:AL2,"Y") for the below:

Is there any way I can do it in Power Query (M) or as a DAX measure? I've managed to create the count for how many time "Y" appears in the entire row but not the specifc range (AI:AL).

Thanks,

Ana

[Ahmedx]

pls try this

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

[AnaR · Accepted solution]

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]

Dear @AnaR 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")))

[AnaR]

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

[DOLEARY85]

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 👍

[AnaR]

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