Is there a Countif() function equivalent in the M language?

[Christann]

I am using the add columns button which uses the M Language, and need an equivalent to Excels Countif(). Any Ideas?

[Anonymous]

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
    countif

[Christann]

@Anonymous

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! 🙂

[Anonymous · Accepted solution]

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

[Christann]

Too bad that will not work the way I hoped. Thanks so much for your help!

[ChrisMendoza]

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.

[Christann]

@ChrisMendoza

I don't think that this is quite what I was looking for, but thank you for the help!