电源查询

电源查询 - 计数是否自定义功能(Power Query - Count if custom function)

继续这个问题 。

我想创建一个自定义函数，我传入一个表，一列和一个值，它会计算满足这些条件的所有行。

到目前为止，我有

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

但是我遇到了各种各样的问题。 第一个是调用函数来测试它，当我传入一个表名的字符串时，它会向函数发送一个文字字符串，而不是将它转换为表。 当通过查询编辑器按钮调用它时，这可能只是一个问题，并且应该在我的M代码的其余部分使用该函数时自行解决。

第二个是如何将col值传递给函数的[col]部分。 我看到其他例子中使用了肥胖的火箭，即[column name] <= col但是无法使其工作。

我努力让自己的脑袋围绕着功能，并且很乐意在vba中这样做，所以如果你有任何提示，请大声喊出来。 这方面的文档很难得到。 干杯。

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.

最满意答案

表可以被认为是记录列表，其中每个记录都是一行。 在Table.AddColumn和Table.SelectRows的函数中， [col]是写入Record.Field(_, "col")一种较短的方式，它访问记录的“col”字段。

这是编写你的函数的一种方法：

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

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