Using Parameter for SQL IF statement in WHERE clause

[Anonymous]

Example:

I have a table with 3 columns (ItemCode, CustCode, ItemGroupType) that I filter using a parameter (dynamic as this is populated by users on adhoc basis) which works a treat to return all Items assigned to that Customer....

WHERE CustCode = '"&UserCustCode&"'

Now however if a user enters a particular code (say 10666 (formatted as Text)) I want to return results based on ItemGroupCode rather than CustCode so M code would look like...

= Table.SelectRows(Source, each (if UserCustCode = "10666" then [ItemGroupCode] = "090" else [CustCode] = UserCustCode))

and this gets the desired results.

So my question is how do I move this into an "SQL format" so I don't have to bring the entire dataset back and filter in PQ? I've googled but can't find a use case of using a PQ parameter within an IF statement for MySQL.

I tried to guess the logic as my SQL knowledge is very limited...

where IF('"&UserCustCode&"' = '10666', ItemGroupCode = '090', CustCode = '"&UserCustCode&"')

but get an error 😞

Syntax error in SQL statement at or about "= '10666', ItemGroupCode = '090', CustCo" (10713)

For extra points a solution that has an IF ELSE type scenario would be ideal as I have at least 2 CustCodes I want this to apply to!

Thanks,

[lbendlin]

= Table.SelectRows(Source, each (if UserCustCode = "10666" then [ItemGroupCode] = "090" else [CustCode] = UserCustCode))

and this gets the desired results.

I doubt that. This is not the right syntax for Table.SelectRows.

In SQL you can use similar logical conditions

WHERE a=b or c=d

Learn SQL, it's not that difficult as it is close to natural language.

[Anonymous]

@lbendlin seems to be giving me the desired results unless I'm missing something??

CustCode 10666 in my parameter returning only 090 regardless of customer:

CustCode 10480 returning all their items regardless of type (they only happened to have 001)

Not sure how to utilise the where & or clause as I want to say IF this then filter based on A else filter based on B don't I?

As for learning SQL I appreicate that but I have the IF clause: 

IF(<condition>, <value if true>, <value if false>)

 but using that format with the PQ parameter seems to be my issue.

Thanks,