Forum Discussion
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 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")
= 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)))
11 Replies
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) ) )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)))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.
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?
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 [email protected] 3 [email protected] 3 [email protected] 3 [email protected] 2 3 [email protected] 2 [email protected] 2 [email protected] 2 [email protected] 1 3
