Hi,
I have a query which pulls data from an access database stored on the network to an excel file using power query, I have added parameters from excel table and it works fine when all the parameter values are passed, what I couldn't solve is that these parameters should be optional, when there is a null value in any of the parameters 2 & 3 then it should return all the records. Appreciate your help to fix this code, thanks.
Parameters-1 = FilePath
Parameters-2 = String 'P4'
Parameters-3 = String 'A123456'
let
Source = Access.Database(File.Contents(fParameters("Parameters",1)), [CreateNavigationProperties=true]),
_TSData = Source{[Schema="",Item="TSData"]}[Data],
#"Filtered Bay & Emp" = Table.SelectRows(_TSData, each ([Bay No] = fParameters("Parameters",2)) and ([Emp No] = fParameters("Parameters",3)))
in
#"Filtered Bay & Emp"
Try by using two separate Filter steps, each testing if the parameter equals null or not. Something like this:
Filter1 = if fParameters("Parameters",2) = null then _TSData else Table.SelectRows(_TSData, each ([Bay No] = fParameters("Parameters",2)),
Filter2 = if fParameters("Parameters",3) = null then Filter1 else Table.SelectRows(Filter1, each ([Emp No] = fParameters("Parameters",3))
Thanks, that helps.
After adding a close bracket for Filter1 & 2 it works.
Filter1 = if fParameters("Parameters",2) = null then _TSData else Table.SelectRows(_TSData, each ([Bay No] = fParameters("Parameters",2))),
Filter2 = if fParameters("Parameters",3) = null then Filter1 else Table.SelectRows(Filter1, each ([Emp No] = fParameters("Parameters",3)))
Great! Well spotted. I couldn't test the code myself and forgot to add the extra parentheses.
crossposted: https://chandoo.org/forum/threads/optional-parameter-from-excel-table.54657/
Please read: https://excelguru.ca/a-message-to-forum-cross-posters/
