COUNTIF in Power Query

[Syndicate_Admin]

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")

[Syndicate_Admin]

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)
                                                      )
                                  )

[Syndicate_Admin]

The syntax is not clear for me = let newcols = 

Could you paste it in the workbook.

= 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=Changed Type(_), 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)))

The M code in my workbook is 

let
    Source = Excel.CurrentWorkbook(){[Name="Table3"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}, {"Column2", type text}, {"Column3", type text}, {"Column4", type text}, {"Column5", type text}, {"Column6", type text}, {"Column7", type text}, {"Column8", type text}, {"Column9", type text}, {"Column10", type text}, {"Column11", type text}, {"Column12", type text}, {"Column13", type text}, {"Column14", type text}, {"Column15", type text}, {"Column16", type text}, {"Column17", type text}, {"Column18", type text}, {"Column19", type text}, {"Column20", type text}, {"Column21", type text}, {"Column22", type text}, {"Column23", type text}, {"Column24", type text}, {"Column25", type text}, {"Column26", type text}, {"Column27", type text}, {"Column28", type text}, {"Column29", type text}, {"Column30", type text}, {"Column31", type text}, {"Column32", type text}, {"Column33", type text}})
in
    #"Changed Type"

[Syndicate_Admin]

For the last column If 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 as I try to add & operator to a number. Is there any work around.

[Syndicate_Admin]

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?

[Syndicate_Admin]

I notice an error. If Columns AC:AF has all Yes the output is still showing as NA instead of X.

Because column O and AC have the same name PQ names AC column name as Text_3. That particular column name is repeted three times. Two are in the selected columns out of 354 columns. How to overcome this?

[Syndicate_Admin]

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.

[Syndicate_Admin]

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?

[Syndicate_Admin]

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))

[Syndicate_Admin · Accepted solution]

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)))