Forum Discussion
Pass parameter from Cell(s)
Is there some way to pass a parameter to PQ from a value in a cell in the spreadsheet? Or prompt the user for values at runtime?
Further, I used to use a tool when I worked with DB2 that would take a list of values in a column of the spreadsheet and pass those to the query as part of the criteria. Is there a way to do this with PQ? Basically take a list of values in a worksheet and use them as the argument of an IN() statement to be sent to SQL server?
6 Replies
Hi domtrump,
yes of course. You can import to power query all tables and named ranges. If you want to pass parameter from excel file to power query - just name ist i.e. myParameter
Then right clik on that cell --> Get Data from Table/Range
In Power Query right clik on value and Drill Down
Now you can use your parameter in SQL query but you need to add it like this (imaine dashes as your SQL code) - but maybe you have to use more quotes. Just try it but it works.
----------" & myParameter & "---------------------
Seems like this would work as a way to pass a single value (from a single cell) to a query. What if I needed to pass many values - a list of values - to the query? For example, If I have a list of 50 customer ID's and I paste them into a range of cells in the Excel workbook, I would want to pass these values to an "IN" statement in the WHERE clause of the query [WHERE CustomerID in (<list of values from range of cells>)] Is this possible?
Exact solution depends on the data type of your list and what syntax your need.
BUT,
Lets say you have a table called Parameters and the column with the parameter values is called Values
The expression Text.Combine(Parameters[Values],”, ")
Will give you a text string of all you parameter values separated by commas.
This works for number values. If you need quotes, you probably need to add a custom column in your Parameters table to include the quotes first.
Hmmm. Not totally sure I follow. Let's say my source table looks like this:
My query to selct a subset of CustomerIDs would look like this (source is a query loading data from SQL Server)
Now I create a new table in Excel called ParmTable. The user pastes the desired list of CustomerIDs into this table.
This table has been loaded to PQ and then I right clicked it and hit Drill Down
So now how would I modify my query to use the list of parameters from ParmTable instead of a hardcoded list?
SqlQuery = "SELECT * FROM Sales where CustomerId in (" & Text.Combine(ParmTable, ",") & ")"
domtrump You can convert that list of values from excel sheet into a List and then by using Table.SelectRows & List.Contains you can filter the rows which database will treat as IN.
Native Query:
