Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
PowerUser39
Frequent Visitor

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?

2 ACCEPTED SOLUTIONS

Thanks Edhans.

 

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

PowerUser39_0-1720716442580.png

 

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

 

PowerUser39_1-1720716636489.png

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

 

PowerUser39_3-1720716768935.png

 

PowerUser39_2-1720716690273.png

 

 

View solution in original post

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.



Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling


Proud to be a Super User!

MCSA: BI Reporting

View solution in original post

10 REPLIES 10
v-heq-msft
Community Support
Community Support

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

vheqmsft_0-1720665644299.png

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

vheqmsft_1-1720665765636.png

 

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

 

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

edhans
Super User
Super User

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.

edhans_0-1720639766768.png

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.



Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling


Proud to be a Super User!

MCSA: BI Reporting

@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

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.

edhans_0-1720713292708.png



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.

 



Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling


Proud to be a Super User!

MCSA: BI Reporting

Thanks Edhans.

 

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

PowerUser39_0-1720716442580.png

 

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

 

PowerUser39_1-1720716636489.png

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

 

PowerUser39_3-1720716768935.png

 

PowerUser39_2-1720716690273.png

 

 

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.



Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling


Proud to be a Super User!

MCSA: BI Reporting

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

 

PowerUser39_0-1720722019169.png

 

Any other way of writing this to get the intended result @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.



Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling


Proud to be a Super User!

MCSA: BI Reporting

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

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

July Newsletter

Fabric Community Update - July 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors