• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Optional Parameter from Excel Table

Muralidaran

New Member
Hi,

Cross Post Link: https://www.myonlinetraininghub.com/excel-forum/power-query/optional-parameter-from-excel-table

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"
 
Thanks, completed as below.

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)))
 
Back
Top