Passing Multiple Values to SQL Query (Note does not use parameter) for use in Where clause

[Anonymous]

I discovered the following technique to pass multiple values to a SQL Query in PQ in Excel

Scenario

Excel Spreadsheet

SQL Server database

In Excel create a table that has a list of the values you want to pass.

Create query over that table and set the data type to text and in the last step convert the result to a List. In Excel this is under the Transfom menu and is titled Convert To List. Save as Only Create Connection type of query.

Now in the SQL Statement in PQ I use a Where fieldname In('A','B')

and put the statement in as follows. I suggest creating the SQL Statement and testing it before altering it as shown below. Be sure it works staticly before altering it to work dynamically.

Where fieldname In('" & Text.Combine(Listname,"','") & "')

Note that in the SQL Statement, the opening and closing single quote character is not in the function Text.Combine

the separator in the Text.Combine is 

','

So if the list had values A and B Text.Combine would return A','B

So the value before the double quote " is a single quote ' same for the ending part of the string so that when the string is completed the value for this part looks like 'A','B' to SQL.

Doing this you don't have to use parameters. Your list can contain lots of values. Be sure that your list is text and if necessary use trim to clean out any white space.

Note if you use this method to insert values into a temp table just be aware that the VALUES has a max limit of 1000 rows.

If you want to just pass one value, be sure your list has only one value and that it is text and the seperator in just "" so nothing gets added to the string.

You will also get warnings about Security and Privacy. In the PQ editor under File go to the Options and Settings / Query Options and set Security and Privacy accordingly. Follow your organizations rules.

[v-sdhruv]

Hi @Anonymous ,

Please post your solution in the community and accept your answer as solution. This will help the other members find it more quickly.

Thank you!!

[DataNinja777]

Hi @Anonymous ,

You can pass multiple values from Excel into a Power Query SQL query without using parameters by converting your Excel table of values into a list and then dynamically injecting those values into the SQL IN clause. First, you create a query in Power Query that pulls in your Excel table (e.g., a table named ProductCodeList with a column ProductCode), and then transform that column into a list with trimmed text values. For example:

let
    Source = Excel.CurrentWorkbook(){[Name="ProductCodeList"]}[Content],
    Trimmed = Table.TransformColumns(Source, {{"ProductCode", Text.Trim}}),
    CodeList = Trimmed[ProductCode]
in
    CodeList

This gives you a list like {"A001", "B002", "C003"}. To use this in your SQL statement, you then wrap each item in single quotes and concatenate them using a comma. You can do this with the following logic in your main query:

let
    CodeList = Excel.CurrentWorkbook(){[Name="ProductCodeList"]}[Content][ProductCode],
    CodeListClean = List.Transform(CodeList, each "'" & Text.Trim(_) & "'"),
    CodeListString = Text.Combine(CodeListClean, ","),
    SQL = "
        SELECT *
        FROM dbo.Products
        WHERE ProductCode IN (" & CodeListString & ")",
    Source = Sql.Database("YourServer", "YourDB", [Query = SQL])
in
    Source

This dynamically builds a valid SQL IN clause like IN ('A001','B002','C003'). Make sure to test the query with static values first to ensure it works. If you're only passing one value, you can modify Text.Combine to use an empty string as the delimiter. You may see a privacy warning in Power Query due to combining Excel and SQL sources; resolve it by setting both to the same privacy level or configuring your privacy settings to ignore warnings if permitted by your organization.

Best regards,

[v-sdhruv · Accepted solution]

Hi @Anonymous ,

Thank you so much for sharing this. This kind of post is incredibly valuable to the community.
Definitely consider turning this into a blog post so others can benefit from your experience more easily

https://community.fabric.microsoft.com/t5/Power-BI-Community-Blog/bg-p/community_blog 

Please consider Accepting as solution to help the other members find it more quickly.

Thanks