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 &"')