Power Query - Count if custom function

Question

Following on from this question.

I'd like to create a custom function where I pass in a table, a column and value and it counts all the rows where those conditions are met.

So far I have

let 
    fCountif = (tbl as table, col as text, value as any) as number =>
    let
        select_rows = Table.SelectRows(tbl, each [col] = value),
        count_rows = Table.RowCount(select_rows)
    in
        count_rows
in
    fCountif

But am running into all sorts of issues. The first is invoking the function to test it, when I pass in a string for the Table name it sends a literal string to the function instead of converting it to table. This is probably only an issue when invoking it through the query editor button and should resolve itself when using the function throughout the rest of my M code.

The second is how to pass the col value into the [col] part of the function. I have seen fat rockets used in other examples ie [column name] <= col but just can't get it to work.

I'm trying hard to get my head around functions and was comfortable doing this in vba so if you have any tips please shout out. Documentation on this is hard to come by. Cheers.

Answer 1

Tables can be thought of as a list of records, where each record is a row. In the functions for Table.AddColumn and Table.SelectRows, [col] is a shorter way of writing Record.Field(_, "col"), which accesses the "col" field of the record.

Here is one way to write your function:

let 
    fCountif = (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
    fCountif

Answer 2

This should work:

let 
  fCountif = (tbl as table, col, value) => 
  let 
   select_rows = Table.SelectRows(tbl, Expression.Evaluate("each "&Text.Replace("["&col&"] = *"&value&"*","*",""""))),
   count_rows = Table.RowCount(select_rows) 
  in count_rows 
in fCountif