powerpivot - Dynamic Excel Power Query -

i have table in excel workbook named "parametertable". in table store parameters want pass sql queries executed via excel power query.

to pull these parameters workbook, use function called "fngetparameter". function code looks this:

let //function name , parameters fngetparameter = (parametername text, columnnum text) => let  //assign table , column function paramsource = excel.currentworkbook(){[name="parametertable"]}[content], paramrow = table.selectrows(paramsource, each ([parameter name] = parametername)),  //assign value return value=  if table.isempty(paramrow)=true     null else if columnnum = "1"     record.field(paramrow{0},"value1") else if columnnum = "2"     record.field(paramrow{0},"value2") else null  in  value  in     fngetparameter 

so, can specify parameter name , column number , function pass when called in other power query queries.

an example call like:

let passedparameter = expression.evaluate("fngetparameter{"paramlabel","1"}",[fngetparameter=fngetparameter]), source = sql.database("servername", "databasename", [query="select * table column1 = " passedparameter" ]) in source 

my problem arises in want save sql query text file , connect text file rather store query in excel. reason, when power query tries call fngetparameter function (which user defined function in workbook), sql query can't seem "see" pq udf.

my main reason doing (saving sql query pqs text file) makes editing query easier in text file , avoids having change query multiple times if other workbooks use similar queries.

thoughts?