count number of occurrences of a field over all rows in a table in query editor

[Anonymous]

Hi there, 

Can someone outline how I would write the equivalent in query editor of below excel example? 

I know how to write this in a measure or by adding a calculated column, but I'd prefer to do so in query editor to allow subsequent logic to work properly. 

thanks!

[tex628]

Column = CALCULATE(COUNTROWS(Table1),ALL(Table1),Table1[Column1] = EARLIER(Table1[Column1]))

[Anonymous]

@tex628 

thanks for this, but are you sure this is meant to work in query editor?  I'm getting error message, first with 'Calculate' and the with 'countrows'. I'm looking for a solution in the query editor environment for my question. 

thanks

[tex628 · Accepted solution]

I apologize, i have a habit of not reading things properly 🙂

Try this as a custom column in queryeditor instead:

List.Count(
    Table.SelectRows(
       #"Previous step is supposed to be here",
        (C) => [Abc] = C[Abc]
    )[Abc]
)

It works fine for me:


More info @ https://stackoverflow.com/questions/53305097/countifs-equivalent-in-power-query-m-counts-per-row-wit...

[Maria1983]

I am sorry to ask 5 years later.. 

Your solution is magnificent and work perfectly, but how can I use this solution if I have multiple criteria? If I want to count if column a AND column b have the same value as the current row. 

Hope you can help, as I have had trouble finding the correct syntax. 

Thank you very much in advance. 

[Anonymous]

@tex628 thanks! this seems to work, albeit not the fastest of solutions.... 😞  but happy it''s working though. I'm not suggesting I fully understand its logic, but for now, I'm happy to be able to proceed.