Forum Discussion

rafaelsmoreno's avatar
rafaelsmoreno
Frequent Visitor
7 years ago
Solved

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.power...
Need Help
  • ImkeF's avatar
    ImkeF
    7 years ago
    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 ;)

rafaelsmoreno's avatar
rafaelsmoreno
Frequent Visitor
7 years ago

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)  :(

ImkeF's avatar
ImkeF
Community Champion
7 years ago

It worked for me.

So next step would be that you post some sample data and the code you've used so far.

  • rafaelsmoreno's avatar
    rafaelsmoreno
    Frequent Visitor
    7 years ago

    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!