if when column in power query

[Anonymous]

Hi all,

I am struggling to get a if when statement to work as a calculated column in power query. the logic I am trying to create is:

if [sales] > 0

where [region] = AMER

and [returns] < 1 where [type] IN 1,2,3,4,5

Then 1 else " "

I am not fully sure if this is even possible in PQ, any suggestions on how to get this logic to work in PQ? 

Thank you!

[V-lianl-msft · Accepted solution]

Hi @Anonymous ,

Try to create a custom column with Power query:

if [Region]= "a" and [Returns] <1 and [Sales]>0 and List.Contains({1,2,3},[Type])
then 1 else ""

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

[Anonymous]

@V-lianl-msft I keep getting this error when using this logic: [Expression.Error] We cannot convert the value null to type Logical.

how fo you handle this? 

[Anonymous]

also thank you @V-lianl-msft 

[rajkmr106]

This is defently  possible in power query, You need to filter the value based on required value and also need to mentioned the condition for it.

[Anonymous]

@rajkmr106  what would the correct logic look like? 

if [sales] > 0

filter [region] = AMER

and [returns] < 1 filter [type] IN 1,2,3,4,5

Then 1 else " " ?