Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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 function using Power Query?
Thanks!
Solved! Go to Solution.
@mrbuttons07
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 🙂
⭕ Subscribe and learn Power BI from these videos
⚪ Website ⚪ LinkedIn ⚪ PBI User Group
Hello @mrbuttons07
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
@mrbuttons07
Can you share some sample data and the expected result to have a clear understanding of your question?
You can save your files in OneDrive, Google Drive, or any other cloud sharing platforms and share the link here.
____________________________________
How to paste sample data with your question?
How to get your questions answered quickly?
_____________________________________
Did I answer your question? Mark this post as a solution, this will help others!.
Click on the Thumbs-Up icon if you like this reply 🙂
⭕ Subscribe and learn Power BI from these videos
⚪ Website ⚪ LinkedIn ⚪ PBI User Group
Hi @Fowmy ,
Since the data is confidential, I have sent a modified excel file for you to have a look at it. FYI, data that I'm using comes from a Sharepoint list connected to Excel.
What I would like to achieve is a COUNTIF Function on columns. If any of these columns contains a "No", then it should count as 1.
I don't think conditional formatting column works because it doesn't seem to count the next column after that. I have thought of using excel formula but it would be easier if I can achieve this in Power Query itself.
Thanks!
Hello @mrbuttons07
you can add this formula to a new column
if List.AnyTrue(List.Transform(Record.ToList(_), each _ = "no"))=true then 1 else 0
this gives you 1 if a "no" is present in your row
here the complete code
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}}),
AddCountIf = Table.AddColumn
(
PreviousStep,
"CountIfNo",
each if List.AnyTrue(List.Transform(Record.ToList(_), each _ = "no"))=true then 1 else 0
)
in
AddCountIf
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
@mrbuttons07
Try this in a blank query and you can copy the following code in blank query and check the steps.
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, "New Col", each List.Count(List.Select(Record.ToList(_), each _ = "NO")))
in
#"Added Custom"
________________________
If my answer was helpful, please mark this post as a solution, this will also help others!.
Click on the Thumbs-Up icon if you like this reply 🙂
⭕ Subscribe and learn Power BI from these videos
⚪ Website ⚪ LinkedIn ⚪ PBI User Group
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?
@mrbuttons07
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 🙂
⭕ Subscribe and learn Power BI from these videos
⚪ Website ⚪ LinkedIn ⚪ PBI User Group
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
101 | |
50 | |
19 | |
12 | |
11 |