Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Multiple CountIf function in Power Query

Hello everyone, I would like to enforce a countif function in from a data source I have. There are multiple columns containing a "Yes", "No" data. Is there a way I can enforce a countif functio...
  • Fowmy's avatar
    Fowmy
    6 years ago

    Anonymous 

    Please try the following code, I have added two columns as requested:

     



    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WcnEMcVTSwUbF6sBl/fxhBLoMsgKIXCwA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t, Column2 = _t, Column3 = _t, Column4 = _t]),
    #"Added Custom" = Table.AddColumn(Source, "1st Two Columns", each List.Count(List.Select(Record.ToList(Record.SelectFields(_,{"Column1","Column2"})), each _ =  "NO"))),
    #"Added Custom1" = Table.AddColumn(#"Added Custom", "2nd Two Columns", each List.Count(List.Select(Record.ToList(Record.SelectFields(_,{"Column3","Column4"})), each _ =  "NO")))
in
    #"Added Custom1"

     

    ________________________

    If my answer was helpful, please mark it as a solution

    Click on the Thumbs-Up icon if you like this reply 🙂

    YouTube
    LinkedIn
     

Jimmy801's avatar
Jimmy801
Icon for Community Champion rankCommunity Champion
6 years ago

Hello Anonymous 

 

you can make a countif with Table.RowsCount and Table.SelectRows. Basically first filter the table and then count the rows.

this code would look like this

CountIf = Table.RowCount(Table.SelectRows(PreviousStep, each [A]="yes" and [B]="yes" and [C]="no"))

 

Here the complete example

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WqkwtVtKBknn5SrE60QgOXATGRlOCIOGKUIXQDY8FAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [A = _t, B = _t, C = _t]),
    PreviousStep = Table.TransformColumnTypes(Source,{{"A", type text}, {"B", type text}, {"C", type text}}), 
    CountIf = Table.RowCount(Table.SelectRows(PreviousStep, each [A]="yes" and [B]="yes" and [C]="no"))
in
    CountIf

 

Copy paste this code to the advanced editor in a new blank query to see how the solution works.

If this post helps or solves your problem, please mark it as solution (to help other users find useful content and to acknowledge the work of users that helped you)
Kudoes are nice too

Have fun

Jimmy