Forum Discussion

PowerUser39's avatar
PowerUser39
Frequent Visitor
2 years ago
Solved

How to count rows with specific value in Power Query

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?

Need Help
  • PowerUser39's avatar
    PowerUser39
    2 years ago

    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's avatar
    edhans
    2 years ago

    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.

10 Replies

  • edhans's avatar
    edhans
    Community Champion
    2 years ago

    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's avatar
      PowerUser39
      Frequent Visitor
      2 years ago

      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's avatar
        edhans
        Community Champion
        2 years ago

        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.

         

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    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's avatar
      PowerUser39
      Frequent Visitor
      2 years ago

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