cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Syndicate_Admin
Administrator
Administrator

COUNTIF in Power Query

PQ.png

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

1 ACCEPTED SOLUTION

Accepted Solutions

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

 

View solution in original post

10 REPLIES 10
Syndicate_Admin
Administrator
Administrator

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

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"

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.

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?

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?

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.

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_0-1652903577554.png

 

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_0-1652947085970.png

PowerBI_Query_1-1652947143931.png

 

 

 

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

 

Helpful resources

Announcements
Power Platform Conf 2022 768x460.jpg

Join us for Microsoft Power Platform Conference

The first Microsoft-sponsored Power Platform Conference is coming in September. 100+ speakers, 150+ sessions, and what's new and next for Power Platform.

May UG Leader Call Carousel 768x460.png

June User Group Leader Call

Join us on June 28 for our monthly User Group leader call!

PA Virtual Workshop Carousel 768x460.png

Register for a Free Workshop

This training provides practical hands-on experience in creating Power Apps solutions in a full-day of instructor-led App creation workshop.

PA.JPG

New Release Planning Portal (Preview)

Check out our new release planning portal, an interactive way to plan and prepare for upcoming features in Power Platform.

Users online (1,646)