Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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.
@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.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
107 | |
98 | |
78 | |
66 | |
53 |
User | Count |
---|---|
144 | |
104 | |
100 | |
86 | |
64 |