Forum Discussion
Countif as a function in M - Power Query
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 ;)
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 Imke! You are the BI Accountant, nice to meet you. I follow you on twitter.
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?
The error I got is that the moment I close the code editor and the query refreshes to show the two new added columns, they are there, but every single field is "ERROR". When I click on them, the circular reference error message pops up.
So, basically, I need to countif in the very query I'm running and I was using this piece of code. If that's not the kind of function that will give me that, what kind of function would that be?
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 ;)
Hi Imke!
I tried this code and now there is no error message in the fields, but all of them came up with 0 (zero) :(