Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago

List In Parameter and ignore when null

I am using Power Query to pull data from SQL.  I have mutliple lists that are being created with the Text.Combine function I found here:  https://community.powerbi.com/t5/Desktop/Using-list-in-parame...
Need Help
lbendlin's avatar
lbendlin
Super User
5 years ago

This part

 

WHERE
p.itemstatusname in('"& ItemStatus & "')
AND CASE WHEN "&SKUList&" IS NOT NULL THEN p.partnumber in("&SKUList&") END

 

has an incomplete CASE statement (the ELSE part is not optional) and is missing the condition. Your case statement needs to either resolve to True/False, or you need to compare it to _something_

Anonymous's avatar
Anonymous
Not applicable
5 years ago

Thanks lbendlin!  I am very new to this and did not know that the ELSE is required.  Does this comparsion CASE WHEN "&SKUList&" IS NOT NULL resolve the True/False?  Is there a way to do nothing with the ELSE portion of the CASE?  All I want to is a WHERE p.partnumber in (SKUList) when the SKUList variable contains multiple records like '123','456','789' and do nothing when SKUList is null.  p.partnumber in (SKUList) (without the CASE WHEN) works just fine when the variable contains values.  If the variable is left blank, I get 0 results but I want to see all results.

 

Any Thoughts?  Thanks again!

  • lbendlin's avatar
    lbendlin
    Super User
    5 years ago

     add a dummy entry to your SKUList to make sure it is never null.