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.
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
RemoveCountNOFinal 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
Anonymous Thanks for your response. I tried it your method too but getting only the entire list count and not the conditional row count.