Forum Discussion
Multiple CountIf function in Power Query
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 🙂
Hi there Fowmy
Your code works! Thanks a lot!
However, I would like to see if I'm able to group the columns. Example, Column 1, Column 2 and Column 3 into a new COUNTIF Column. Then Column 4, Column 5 and Column 6 into another new COUNTIF Column.
With you current code, do I need to separate each columns into separate subset code?
Super UserAnonymous
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 🙂
Hi Fowmy
Thanks a lot! It works!