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-parameter-in-M-language/m-p/454178.  This works great but in my situation, i want to be able to ignore the related WHERE  IN if the parameter list is null.  I convert the Text.Combine to look like this in insure a null value parameter.

 

SKUList = if Text.Length(Text.Combine(#"tMultiSKU"[SKU List]))=0 then null else Text.Combine( List.Transform(#"tMultiSKU"[SKU List], each "'"&_&"'"), ",")

 

This appears to be assigning the variable properly for both null and the list filled in.  I tried to write a CASE WHEN within the WHERE clause to not search for this parameter if null.  Now that I added the CASE WHEN to the p.partnumber in(), I am getting an error that states An expression of non-boolean type specified in a context where a condition is expected, near ','.  

 

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

 

Is there a way around the error or a better way to ignore a null parameter?

Need Help

3 Replies

  • 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.