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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Christann
Helper IV
Helper IV

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?

1 ACCEPTED SOLUTION
Anonymous
Not applicable

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

View solution in original post

6 REPLIES 6
Anonymous
Not applicable

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

@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
Not applicable

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

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

ChrisMendoza
Resident Rockstar
Resident Rockstar

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):

 1.PNG

Heck it even guessed what I wanted to use from a small sample. Try it out, maybe it might work for your needs.

2.PNG

 

If that does not work, then you should post some data so we can try and assist.






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!

Proud to be a Super User!



@ChrisMendoza

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

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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