Countifs with multiple conditions in PowerQuery in PB

[Anonymous]

Hello Wonderful people

An example of the countifs formula with multiple conditions I am currently using in Excel is given below.  I wish to replicate the same formula in PowerQuery in PB:

=COUNTIFS(Append1!$BF:$BF,">=01/01/2020",Append1!$BF:$BF,"<18/11/2022",Append1!$D:$D,"<>24 Application withdrawn")

At the moment, I am using the filter option to filter out the 24 Application withdrawn condition but it's better to use a SQL to do the job!

I know it's NOT going to be easy in PB.

Thank you in advance.

[Anonymous]

@neekotang, thank you.  This works in SSMS.  

[Anonymous]

@neeko tang, does your syntax work in PowerBI or SSMS?

[Anonymous · Accepted solution]

Hi @Anonymous ,

This is my test data.  

Best Regards,

Neeko Tang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly. 

[Anonymous]

Hi @Anonymous ,

You can use the following SQL statements.

select count(*)
from table
where date >= '2020-01-01' and date <= '2022-11-18' and column <> '24 Application withdrawn'

You also can refer to the following posts that may be helpful to you:

SQL Countif function - Stack Overflow

sql - How to do countIf() in Oracle - Stack Overflow

COUNTIF in SQL: COUNT(CASE WHEN … THEN 1 END) (modern-sql.com)

Best Regards,

Neeko Tang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly. 

[Bifinity_75]

Hi @Anonymous , If I have understood you correctly, and assuming that your data model is something similar to this:

- Create a custom column, and insert this formule:

if [Date]>= Date.FromText("2020-01-01") and
[Date]<= Date.FromText("2022-11-18") and
[Column]<>"<> 24 Application withdrawn" 
then 1 else 0

- The result:

Best regards