Forum Discussion
How to count rows with specific value in Power Query
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:
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.
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.
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.
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.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:
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.