Forum Discussion
Countif as a function in M - Power Query
Hi
I am trying to create a function that would emulate the countif from excel into power query. I tried the code at this post below, but it is just not working for me.
This is the code:
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
countifAll I got is a circular reference. Can any of you guys help me on that?
In time: I don´t want to solve it by grouping the table, it has to be a function that will bring the count of occurences based on the target column.
Thanks in advance,
rafaelsmoreno wrote:
...
Base_documentos is the query in which I am running the code. Is that a problem? Should I place instead of Base_documentos the previous stage of the query?
...
Yes, that's causing the circular dependency error.
As you said: Take the previous step name instead.
Actually: You should take the buffered previous step name:
let
...
#"Personalização Adicionada3" = Table.AddColumn(#"Índice Adicionado","contCHAVE", each fcountif(Table.Buffer(PreviousStepName), "CHAVE", [Índice])),
#"Personalização Adicionada4" = Table.AddColumn(#"Personalização Adicionada3","contCHAVEOR", each fcountif(Table.Buffer(PreviousStepName), "CHAVEOR", [Índice]))
in
#"Personalização Adicionada4"Thanks for the follow ;)
10 Replies
Invoking ImkeF
That function works for me (technically). So your error must come from the parameters you've provided.
Can you share your query code or give an example for before and desired after please?
Hi
That's the code, below.
let
...
#"Personalização Adicionada3" = Table.AddColumn(#"Índice Adicionado","contCHAVE", each fcountif(Base_documentos, "CHAVE", [Índice])),
#"Personalização Adicionada4" = Table.AddColumn(#"Personalização Adicionada3","contCHAVEOR", each fcountif(Base_documentos, "CHAVEOR", [Índice]))
in
#"Personalização Adicionada4"Base_documentos = name of the table
"CHAVE" and "CHAVEOR" are the names of the columns I want to countif
Índice = Index (from 1)
Basically I want to add two new columns, one that will be a countif to the column CHAVE (contCHAVE) and the other to be a countif of column CHAVEOR (contCHAVEOR). From that on I'll add another column with a logical test (if then else) so I can in the end apply the exact filter I need.
One thing that I think might be wrong is the third parameter of the function. I am not sure what to place there. In the first parameter I put the table name, in the second parameter I put the Column in which I want to apply the countif, and in the third, well, I put the index column. Not sure, but seemed the right thing based on the excel file I got from the blog of the author of the other post from which I got the function.
Thanks in advance,
Code looks perfectly fine to me.
Does error arise when you click on the step #"Personalização Adicionada4" or at a later stage?
Does the table "Base_documentos" depend on this query?
Hi rafaelsmoreno,
Have you solved your problem?
If you have solved, please always accept the replies making sense as solution to your question so that people who may have the same question can get the solution directly.
Best Regards,
Cherry
