Forum Discussion
Solved
COUNTIF in Power 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 f...
= 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)))
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)
)
)
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.