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

pbrayudu

New Member
Joined
Dec 31, 2011
Messages
37
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.
 

Excel Facts

When did Power Query debut in Excel?
Although it was an add-in in Excel 2010 & Excel 2013, Power Query became a part of Excel in 2016, in Data, Get & Transform Data.

Watch MrExcel Video

Forum statistics

Threads
1,132,672
Messages
5,654,665
Members
418,147
Latest member
dorkas

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top