Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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.
User | Count |
---|---|
99 | |
90 | |
85 | |
74 | |
67 |
User | Count |
---|---|
114 | |
103 | |
100 | |
72 | |
64 |