The ultimate Fabric, Power BI, SQL, and AI community-led learning event. Save €200 with code FABCOMM.
Get registeredCompete to become Power BI Data Viz World Champion! First round ends August 18th. Get started.
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!
Solved! Go to 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...
Column = CALCULATE(COUNTROWS(Table1),ALL(Table1),Table1[Column1] = EARLIER(Table1[Column1]))
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
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...
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.
You could create three columns: one to calculate column A, one to calculate column B, and then another to compare the two using if [column A] = [column B] then "Yes" else "No". It might not be the most efficient or clean but it's a work around if you can't figure out the syntax.
@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.