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