Passing multiple values in excel table as parameters into query

[KeyserSoze33]

Hello,

I am building a workbook for our accounting team that queries the database and returns a table from which several analytics are performed. I have previously made it so that a user can input a year and an asset id into a table, refresh, and the appropriate data is returned. I now am trying to modify it so that multiple asset ids can be fed into the query to return regional or market data.

Before, I created a table  and built a function from it (using some tutorial I found as I'm pretty new to using power query):
ParameterValue

Year	2023
Asset	2901

and the funciton:
(ParameterName as text) =>
let
ParamSource = Excel.CurrentWorkbook(){[Name="Parameters"]}[Content],
ParamRow = Table.SelectRows(ParamSource, each ([Parameter] = ParameterName)),
Value=
if Table.IsEmpty(ParamRow)=true
then null
else Record.Field(ParamRow{0},"Value")
in
Value

Within the query here's how it's defined:

let
Year = fnGetParameter("Year"),
Asset = fnGetParameter("Asset"),

dbQuery = "

DECLARE @PeriodYear nvarchar(4)
SET @PeriodYear = "&Year&"
DECLARE @Company nvarchar(5)
SET @Company = "&Asset&"
...

Source = Sql.Database("...", "...",[Query=dbQuery])
in
Source

First thing I tried was just adding another row to the parameter table with the same "Asset" name with a different value and then modifying the query to better accept multiple value (assuming more than one was being passed through in the first place). in the end, it didn't work and here I am. Any help would be appreciated. Thank you

[rubayatyasmin · Accepted solution]

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
Values

This 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
Source

This 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.

[KeyserSoze33]

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]&")")

[rubayatyasmin]

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.