Title: Excel 2010 Power query: Change a parameter in SQL query using List values or Value in Cell of sheet1.

[pbrayudu]

Sir,

I am using Microsoft Excel 2010 with Power query add in (June 2014 released version). I am extracting the data into Excel sheet using SQL query ( OLE-DB Query). I have been successful in getting the results.
Every time, i need to change a parameter in Source query to obtain the required results. Query is shown below.

Select distinct Workorder.location as "Equipment",
workorder.wonum as "Work Order",
workorder.actstart,
Workorder.description as "WO Description",
convert (varchar(20),Workorder.actstart,101) as "Start Date",
Workorder.worktype as "Work Type",
workorder.actlabhrs AS [Man Hours],
workorder.actlabcost AS [Man Power Cost],
Workorder.actmatcost as "Material Cost",
Failureremark.description as "Short History"
from workorder left join locations on workorder.location = locations.location
left join failureremark on workorder.wonum = failureremark.wonum
where workorder.siteid = 'adrd'
AND workorder.location = 'U5105LJ'
AND workorder.worktype in ('pd','cm','md','pm')
AND workorder.actstart >='01/01/1999 00:00:00'
And workorder.actstart <= '04/30/2009 23:59:59'
AND workorder.woclass = 'workorder'
order by workorder.actstart desc;

I would like to define a parameter for "Workorder.location" (present inside where clause) in Sheet1 of the same excel workbook (Can be single value or List values") so that the parameter in the query is changed automatically and runs to give the result.

Please assume server name: "00.00.000.000" and database name "qwerty".

I am basic user of Microsoft Power query. I am soliciting to have one of the following solutions.
1. I am requesting to have solutions either a "Function inside Power query", so that it can be invoked as required.
2. VBA code
3. Any other.
I thank you all in advance.
regards

P. Bangaru Rayudu
Reliability Cost Engineer
Abu Dhabi; UAE.