Forum Discussion
Is there a Countif() function equivalent in the M language?
I am using the add columns button which uses the M Language, and need an equivalent to Excels Countif(). Any Ideas?
As far as I know, in Power Query, you cannot use a column from a different table directly. My solution is to merge these two tables together.
You can refer to MS doc regarding how to combine and merge data in Power Query.
https://docs.microsoft.com/en-us/power-bi/desktop-shape-and-combine-data
6 Replies
You can follow the information below to create a custom function in M language and use it as Countif.
http://jaykilleen.com/posts/countif-in-power-query-or-powerbi-using-m-language
The simple code is:
let countif = (tbl as table, col as text, value as any) as number => let select_rows = Table.SelectRows(tbl, each Record.Field(_, col) = value), count_rows = Table.RowCount(select_rows) in count_rows in countifAnonymous
Hi! Thanks for the feedback! I tried the function you inserted, but it would not let me use a column from a different table. Do you know how to fix this?
P.S. I have never used custom functions before, so it is great to learn about them! :)
As far as I know, in Power Query, you cannot use a column from a different table directly. My solution is to merge these two tables together.
You can refer to MS doc regarding how to combine and merge data in Power Query.
https://docs.microsoft.com/en-us/power-bi/desktop-shape-and-combine-data
hello Christann,
Using the Column From Examples, in my simple example I had already setup, the suggested was an if statement.
Specifically:
= Table.AddColumn(Table1_Table, "Custom", each if [In] = "IN" then 1 else if [In] = "OUT" then 0 else null, type number)
Produced (in last img you can see that I told it to count "IN" as 1 and "OUT" as 0):
Heck it even guessed what I wanted to use from a small sample. Try it out, maybe it might work for your needs.
If that does not work, then you should post some data so we can try and assist.
I don't think that this is quite what I was looking for, but thank you for the help!
