COUNTIF in Power Query

[PowerBI_Query]

I have data from A:AG in power query. Workbook link
I need to add six new columns for six color coded ranges from AH:AM.
Each field in those columns should include a equivalent of the excel formula
AH >> =IF(COUNTIF(A2:B2,"Yes")+COUNTIF(A2:B2,"No")=2,"X","NA")
AI >> =IF(COUNTIF(C2:C2,"Yes")+COUNTIF(C2:C2,"No")=1,"X","NA")
AJ >> =IF(COUNTIF(D2:H2,"Yes")+COUNTIF(D2:H2,"No")=5,"X","NA")
etc
Last column counts NO's if there are any in the range A:AF
=IFERROR(CHOOSE(COUNTIF(A2:AF2,"No"),1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,26,27,28,29,30,31,32),"None")

[Anonymous]

In Power BI I want to count how many rows a specific e-mail address occurs on. It's almost the same as COUNT.IF in Excel. I would like to have this as a separate column in the Power Query table without have to group the table since I need all rows even if e-mail is missing.

This is very simple in Excel and i feel that it must be a easy way to do this also in Power BI, but I get stucked. Please help! 🙂

Below are an example what I would like to achive.

	3
email1@domain.com	3
email1@domain.com	3
email1@domain.com	3
email2@domain.com	2
	3
email2@domain.com	2
email3@domain.com	2
email3@domain.com	2
email4@domain.com	1
	3

[Anonymous]

I solved it by this formula in the Table (not in Power Query):

E-mail occurences = COUNTX(FILTER(Customer,EARLIER(Customer[E-Mail])=Customer[E-Mail]),Customer[E-Mail])

[wdx223_Daniel]

NewStep = let
                 newcols={"First","Second","Third","Fourth","Fifth","Sixth","Count"},
                 keypos={2,3,8,14,28}
              in
                 Table.FromRecords(
                                   Table.TransformRows(
                                                       PreviousStepName,
                                                       each let
                                                               a=Record.ToTable(_),
                                                               b=Table.Group(
                                                                             Table.AddIndexColumn(a,"idx"),
                                                                             "idx",
                                                                             {"n",each if List.RemoveItems([Value],{"Yes","No"})={} then "X" else "NA"},
                                                                             0,
                                                                             (x,y)=>Byte.From(List.Contains(keypos,y))
                                                                            ),
                                                               c=List.Count(List.Select(a[Value],each _="No"))
                                                            in _&Record.FromList(b[n]&{if c=0 then "None" else c},newcols)
                                                      )
                                  )

[PowerBI_Query]

Finally! I had to create newcols "Seventh" and new keypos 32. Then sixth column would update. Could you tell me if there is way to get rid of seventh column at the end within the same step/code without adding a new step to remove seventh column via GUI?

= let newcols={"First","Second","Third","Fourth","Fifth","Sixth","Seventh,"Count"},keypos={2,3,8,14,28,32} in Table.FromRecords(Table.TransformRows(#"Changed Type",each let a=Record.ToTable(_),b=Table.Group(Table.AddIndexColumn(a,"idx"),"idx",{"n",each if List.RemoveItems([Value],{"Yes","No"})={} then "X" else "NA"},0,(x,y)=>Byte.From(List.Contains(keypos,y))),c=List.Count(List.Select(a[Value],each _="No"))in _&Record.FromList(b[n]&{if c=0 then "None" else c},newcols)))

[wdx223_Daniel · Accepted solution]

= let newcols={"First","Second","Third","Fourth","Fifth","Sixth","Count"},keypos={2,3,8,14,28,32} in Table.FromRecords(Table.TransformRows(#"Changed Type",each let a=Record.ToTable(_),b=Table.Group(Table.AddIndexColumn(a,"idx"),"idx",{"n",each if List.RemoveItems([Value],{"Yes","No"})={} then "X" else "NA"},0,(x,y)=>Byte.From(List.Contains(keypos,y))),c=List.Count(List.Select(a[Value],each _="No"))in _&Record.FromList(List.RemoveLastN(b[n])&{if c=0 then "None" else c},newcols)))

[PowerBI_Query]

The problem is with the below step. It starts from 0 (groupKind) and ends at 28 leaving 29, 30, 31. y is just a place holder. Not sure how to fix this issue.

(x,y)=>Byte.From(List.Contains(keypos,y))

[PowerBI_Query]

I try to break up Table.Group step but its not clear why last Sixth column is NA. I assume that y is just part of the syntax like a place holder. Why sixth column won't update?

[PowerBI_Query]

Problem is with the last range AC:AF rest are all updating well. Sixth column is NA all the time. I tried to add 0 in keypos={0,2,3,8,14,28} it did not help. Could you fix the issue? or explain what to do.

[PowerBI_Query]

Is is possible to put comments to your code. So that I could reuse the code if I understand.
1.) Mainly, I don't understand the let and in usage in the code. Why let was used with three variables a,b,c in Table.TransformRows - transform as function parameter and in keyword in fourth parameter with _& (_ is element).
2.) List.RemoveItems([Value],{"Yes","No"})={} then "X" else "NA"} but List.Remove accepts list but [Value] is in still in table format under variable a.
3.) Did not understand how 4th parameters work in Table.Group what is y value?

[PowerBI_Query]

For the last column I need to include count plus some text. So I added &"Custom Text"

{if c=0 then "None" else c &"Custom Text"}

It did not work cannot apply operator to type Number and Text. Is there any work around.

[PowerBI_Query]

It worked!

I need to understand it and I will get back to you.

= let newcols={"First","Second","Third","Fourth","Fifth","Sixth","Count"},keypos={2,3,8,14,28} in Table.FromRecords(Table.TransformRows(#"Changed Type",each let a=Record.ToTable(_),b=Table.Group(Table.AddIndexColumn(a,"idx"),"idx",{"n",each if List.RemoveItems([Value],{"Yes","No"})={} then "X" else "NA"},0,(x,y)=>Byte.From(List.Contains(keypos,y))),c=List.Count(List.Select(a[Value],each _="No"))in _&Record.FromList(b[n]&{if c=0 then "None" else c},newcols)))