Forum Discussion
Passing multiple values in excel table as parameters into query
Hi, KeyserSoze33
From what you've described, you've set up a parameter table and a function to return single parameter values from that table, which is then used in a SQL query. To modify this to allow for multiple asset IDs to be input and queried, you'll need to change a few things.
First, your parameter function currently is set to return a single value for a given parameter name. You'll need to modify this function to return a list of values if there are multiple rows in the parameter table with the same name.
Here's an example of how you might do this:
(ParameterName as text) =>
let
ParamSource = Excel.CurrentWorkbook(){[Name="Parameters"]}[Content],
ParamRows = Table.SelectRows(ParamSource, each ([Parameter] = ParameterName)),
Values=
if Table.IsEmpty(ParamRows)=true
then null
else Table.Column(ParamRows,"Value")
in
ValuesThis will return a list of values for a given parameter name, or null if no values are found.
Next, you'll need to modify your SQL query to handle multiple asset IDs. Assuming your database is using SQL Server, you can use the IN keyword to specify multiple values for a condition.
Here's an example of how you might modify your query:
let
Year = fnGetParameter("Year"),
Assets = fnGetParameter("Asset"),dbQuery = Text.Format("
DECLARE @PeriodYear nvarchar(4)
SET @PeriodYear = '"&Year&"'
DECLARE @Assets TABLE (ID nvarchar(5))
INSERT INTO @Assets (ID) VALUES ('"&Text.Combine(Assets,"'), ('")&"')
...",{})
Source = Sql.Database("...", "...",[Query=dbQuery])
in
SourceThis creates a table variable @Assets and inserts the asset IDs into it. You can then join or filter based on this table variable in your SQL query.
Please be aware that this assumes your asset IDs are text (nvarchar) and you might need to modify this to suit your database schema.
Nice, I had an error pop up saying "cannot apply operator & to types text and list". It kept pointing to the Year variable. I played around changing from text to int, removing the apostrophe's , to no avail. I had to hard code it just to push through with the restof the fix. I imagine it's either because we're now feeding arrays and would need to adjust the year variable in the qry to be a table in the same way as Assets, or because the change on the parameter function doesn't allow multi parameters anymore?
| Parameter | Value |
| Year | 2023 |
| Asset | 2901 |
| Asset | 2601 |
| Asset | 2611 |
| Asset | 1721 |
I did find another workaround by feeding everything through a single cell. Like you had suggested, I also created a table in the qry to accept multiple values. In the workbook I created a separate table to list out the Asset ids and in the parameter table had a formula for the value: =TEXTJOIN(",",TRUE,"("&Assets[AssetIDs]&")")
you're getting the error "cannot apply operator & to types text and list" because the function fnGetParameter("Year") is now returning a list instead of a single value due to the modification in the function. From your table, it appears that the "Year" parameter is intended to be a single value, not a list.
A way to fix this would be to create two separate functions - one for getting a single parameter value and another for getting a list of parameter values. Let's call the function for getting a single parameter value fnGetSingleParameter and the function for getting a list of parameter values fnGetMultipleParameters.
Regarding the TEXTJOIN workaround, if it meets your requirements and is easier for you to manage, that's perfectly fine. It's a creative solution! However, it's somewhat less flexible than having a full table of parameters, and also relies on the TEXTJOIN function, which may not be available in all versions of Excel.