Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
I am using the add columns button which uses the M Language, and need an equivalent to Excels Countif(). Any Ideas?
Solved! Go to Solution.
As far as I know, in Power Query, you cannot use a column from a different table directly. My solution is to merge these two tables together.
You can refer to MS doc regarding how to combine and merge data in Power Query.
https://docs.microsoft.com/en-us/power-bi/desktop-shape-and-combine-data
You can follow the information below to create a custom function in M language and use it as Countif.
http://jaykilleen.com/posts/countif-in-power-query-or-powerbi-using-m-language
The simple code is:
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
@Anonymous
Hi! Thanks for the feedback! I tried the function you inserted, but it would not let me use a column from a different table. Do you know how to fix this?
P.S. I have never used custom functions before, so it is great to learn about them! 🙂
As far as I know, in Power Query, you cannot use a column from a different table directly. My solution is to merge these two tables together.
You can refer to MS doc regarding how to combine and merge data in Power Query.
https://docs.microsoft.com/en-us/power-bi/desktop-shape-and-combine-data
Too bad that will not work the way I hoped. Thanks so much for your help!
hello @Christann,
Using the Column From Examples, in my simple example I had already setup, the suggested was an if statement.
Specifically:
= Table.AddColumn(Table1_Table, "Custom", each if [In] = "IN" then 1 else if [In] = "OUT" then 0 else null, type number)
Produced (in last img you can see that I told it to count "IN" as 1 and "OUT" as 0):
Heck it even guessed what I wanted to use from a small sample. Try it out, maybe it might work for your needs.
If that does not work, then you should post some data so we can try and assist.
Proud to be a Super User!
I don't think that this is quite what I was looking for, but thank you for the help!
User | Count |
---|---|
124 | |
108 | |
99 | |
65 | |
62 |
User | Count |
---|---|
137 | |
116 | |
102 | |
71 | |
61 |