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 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) :(
It worked for me.
So next step would be that you post some sample data and the code you've used so far.
Hi, sorry for taking so long to reply. I got into other tasks and had to let it go for a while.
I just tried it again changing some parameters and it finally worked. Here is what I was doing wrong:
In this example I made a query in a n excel file that had only two columns, ProductID (50 lines with repeated items) and ExcelCountif (I wanted to have a way to compare the outcome of PQ Function)
Function 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
countifQuery Code
let
Source = Excel.Workbook(file address),
tbProductID_Table = Fonte{[Item="tbProductID",Kind="Table"]}[Data],
alteredtype = Table.TransformColumnTypes(tbProductID_Table,{{"ProductID", Int64.Type}}),
index = Table.AddIndexColumn(alteredtype, "Index", 1, 1),
pqcountif = Table.AddColumn(alteredtype, "PQCountif", each fcountif(index, "ProductID", [ProductID]))
in
pqcountifThis way it worked. What was wrong before:
pqcountif = Table.AddColumn(alteredtype, "PQCountif", each fcountif(index, "ProductID", [Index]))
As I was placing Index as the value of the Index Column (that I created in PQ only) instead of ProductID in brackets, the outcome of the countif function was always zero. No it worked.
Thanks for your help!