Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
rafaelsmoreno
Frequent Visitor

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.

 

https://community.powerbi.com/t5/Desktop/Is-there-a-Countif-function-equivalent-in-the-M-language/m-...

 

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
    countif

All 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,  

1 ACCEPTED SOLUTION


@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 😉

Imke Feldmann (The BIccountant)

If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!

How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries

View solution in original post

10 REPLIES 10
v-piga-msft
Resident Rockstar
Resident Rockstar

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

Community Support Team _ Cherry Gao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
ImkeF
Super User
Super User

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?

Imke Feldmann (The BIccountant)

If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!

How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries

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?

Imke Feldmann (The BIccountant)

If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!

How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries

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 😉

Imke Feldmann (The BIccountant)

If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!

How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries

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.

Imke Feldmann (The BIccountant)

If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!

How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries

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
countif

 

Query 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
pqcountif

 

This 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!

Greg_Deckler
Super User
Super User

Invoking @ImkeF


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.