If statements in Where Condition with possible empty or Null parameters Power Query Excel SQL

Question

I have a parameter in an Excel spreadsheet for serial number and work order number. Lets say the Cell itself is named SerialNumber and WorkOrderNumber

Note that sometimes we know a value for serial and or work order but sometimes we don't

Using a Get Parameter function in PowerQuery

    let GetValue=(rangeName) => 
        let
          name = Excel.CurrentWorkbook(){[Name=rangeName]}[Content],
          value = name{0}[Column1]
        in
          value
        in GetValue

I was able to get the SerialNumber and WorkOrderNumber as a parameter named SerialNumberP and WorkOrderNumberP in the let statement below:

    Let

      SerialNumberP = GetParamater("SerialNumber"),
      WorkOrderNumberP = GetParameter("WorkOrderNumber")

Then I have an SQL Select statement as such:

    Select
       a.SerialNumber,
       b.WorkOrderNumber,
       a.PartNumber,
       a.BatchNumber,
       a.CreateDate,
       b.CreatedBy,
       b.ComponentSerialNumber,

Our database is in an SQL Database in the network therefore my From statement is:

    From [Database].[Category].[Table1] as a 
    inner join [Database].[Category].[Table2] as b 
    on a.WorkOrderNumber = b.WorkOrderNumber

Now I want to use a where statement where I can search for Either a known SerialNumber Or a WorkOrderNumber, Or both if applicable. However when I use the following code:

    Where
    a.SerialNumber in ('"& SerialNumberP &"') 
    and b.WorkOrderNumber in ('"& WorkOrderNumberP &"')

It picks up that when one field is empty (Either SerialNumber or WorkOrderNumber), that the empty field is null and the query does not pass.

The error i get is :DataSource.Error: Microsoft SQL: This database function doesn't support the query option 'Query' with value 'null'. Whereas if i fill in both fields it works

What would be the best way around this?

Thanks in advance

Edit: I have tried using If statements in the Where conditions and it doesn't seem to work

e.g.

    Where
    If SerialNumberP Is Not Null Then a.SerialNumber In ('"& SerialNumberP &"')
    If WorkOrderNumberP Is Not Null Then b.WorkOrderNumber In ('"& WorkOrderNumberP &"')

Cross posted on TechNet: social.technet.microsoft.com/Forums/en-US/… — MarcelBeug

Alexis Olson Alexis Olson · Accepted Answer · 2017-12-06T23:46:32

Give this a try:

Where
(a.SerialNumber Is Null Or a.SerialNumber in ('"& SerialNumberP &"')) and
(b.WorkOrderNumber Is Null Or b.WorkOrderNumber in ('"& WorkOrderNumberP &"'))

If statements in Where Condition with possible empty or Null parameters Power Query Excel SQL

1 Answers