How to count rows with specific value in Power Query

[PowerUser39]

I have a DAX calculated column to count the rows of the table with specific value in a column and it works fine. Now I want the same to be written in Power Query's custom column but getting error.

My DAX query is

CALCULATE(COUNTROWS('Table'), 'Table'[TEST] = "No")

The above calcultation returns the number of rows in the Table table that has "No" value in the TEST column

I tried the below in Power Query but it is giving me the "Expression.Error: A cyclic reference was encountered during evaluation" error.

Table.RowCount(
       Table.SelectRows(#"Table",
              each Text.Contains ([TEST], "No")))

How to fix this?

[PowerUser39 · Accepted solution]

Thanks Edhans.

I am creating this Row count column #"Added Custom 8" only after the custom column #"Added Custom 7" I created previously:

And I am creating the custom column from Add Column ribbon -> Custom Column and below is the code I have:

And when I click OK and run, getting the below error:

[Anonymous]

Hi @PowerUser39 ,
Thanks for @edhans  reply.
Loop dependencies usually occur when you try to refer to yourself in the same step or use undefined values in a calculation. To avoid this problem, you can break the calculation into multiple steps.
Sample data

I create a new column named TEST.1 and calculate the sum of NO

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W8vNXitWJVop0DQbTUC6aKJiOBQA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [TEST = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"TEST", type text}}),
    AddCustom = Table.AddColumn(Source, "TEST.1", each [TEST]),
    CountNO = Table.AddColumn(AddCustom, "CountNO", each if [TEST.1] = "NO" then 1 else 0),
    TotalNO = List.Sum(CountNO[CountNO]),
    AddResult = Table.AddColumn(CountNO, "Result", each TotalNO),
    RemoveCountNO = Table.RemoveColumns(AddResult, {"CountNO"})
in
    RemoveCountNO

Final output

Best regards,
Albert He

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

[PowerUser39]

@Anonymous Thanks for your response. I tried it your method too but getting only the entire list count and not the conditional row count.

[edhans]

Not sure why it isn't working. Do you have an "each" operator before the Table.RowCount()

Here is full code that works:

let
  Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WSlSK1YlWSgKTyWAyBUymgsm0ovx0MIOAMjCZnp+ThsRFI2MB", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Product = _t]),
  #"Added custom" = 
    Table.AddColumn(
        Source,
        "Custom", 
        each 
        Table.RowCount(Table.SelectRows(Source, each Text.Contains([Product], "f")))
      )
in
  #"Added custom"

there are 7 records that have an "f" in them.

How to use M code provided in a blank query:
1) In Power Query, select New Source, then Blank Query
2) On the Home ribbon, select "Advanced Editor" button
3) Remove everything you see, then paste the M code I've given you in that box.
4) Press Done
5) See this article if you need help using this M code in your model.

[PowerUser39]

@edhans Thanks for your response. Your sample code works fine and I found the problem on my end. 

The TEST column I have in my condition is another custom Added column which is not in the source.

Table.RowCount(
       Table.SelectRows(Source,
              each Text.Contains ([TEST], "No")))

So my question is how to create a custom column in Power Query to count rows in the table that has "No" value in another  custom column?

Please help

[edhans]

Not sure I understand. Your Row Count/Table Select should be after you have this other custom column created. So look at this code:

let
  Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WSlSK1YlWSgKTyWAyBUymgsm0ovx0MIOAMjCZnp+ThsRFI2MB", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Product = _t]),
  AddNewColumn =
    Table.AddColumn(
      Source,
      "New Column",
      each if [Product] = "a" then "f" else [Product]
    ),
  #"Added custom" = 
    Table.AddColumn(
        AddNewColumn,
        "Custom", 
        each 
        Table.RowCount(Table.SelectRows(AddNewColumn, each Text.Contains([New Column], "f")))
      )
in
  #"Added custom"

The #"Added Custom" column where the Row Count and Table Select Rows is happening is referencing the AddNewColumn staep above where a new column was created that changes the "a" to an "f"

So the count goes from 7 to 9.

Please post more than just a formula for your custom column. You'll notice I am posting the code from the advanced editor. We have no idea what steps are before and after your custom column function, and that knowledge is critical to building it correctly.

[PowerUser39 · Accepted solution]

Thanks Edhans.

I am creating this Row count column #"Added Custom 8" only after the custom column #"Added Custom 7" I created previously:

And I am creating the custom column from Add Column ribbon -> Custom Column and below is the code I have:

And when I click OK and run, getting the below error:

[edhans · Accepted solution]

Text.Contains is wrong. It should be Text.Contains([Test], "No") 
You have one argument with an equal sign. Get rid of that and replace with a comma, for 2 arguments.

[PowerUser39]

Ok I changed it and it worked but the result is basically the entire row count in the table and not based on the condition we have to count only the rows with "No" value in TEST column. I want the same DAX output (116) in Power Query and not the entire table row count which is 8940

Any other way of writing this to get the intended result @edhans?

[edhans]

You aren't sharing enough information. In my screenshots I am showing you the table and the answer. 17 records, 9 that match (in the second example). It isn't returning 17, the entire table record count. Showing two cards on the report with different values isn't helpful. What are those measures? Are you adding up, in my example, the 9 17 times and not just getting the distinct value?

How to get good help fast. Help us help you.

How To Ask A Technical Question If you Really Want An Answer

How to Get Your Question Answered Quickly - Give us a good and concise explanation
How to provide sample data in the Power BI Forum - Provide data in a table format per the link, or share an Excel/CSV file via OneDrive, Dropbox, etc.. Provide expected output using a screenshot of Excel or other image. Do not provide a screenshot of the source data. I cannot paste an image into Power BI tables.

[PowerUser39]

@edhans Thanks for the detailed explanation. I rebuilt the report again with the required columns alone with your approach and it worked.