Using Excel Column for Dynamic SQL Power Query

[JonathanLewis]

I am having difficulty using an existing excel column to create a dynamic SQL power query. In the below link there are two example to make this work.

https://techcommunity.microsoft.com/t5/BI-and-Data-Analysis/Create-dynamic-Power-Query-SQL-by-using-...

I am still receiving an error after following the links instructions.

I am trying to do this using the Value.NativeQuery route and am receiving the following conversion error.

DataSource.Error: Microsoft SQL: Conversion failed when converting the nvarchar value '('MyDataStringHere)' to data type int.
Details:
DataSourceKind=SQL
DataSourcePath=sysqlwh1;PPI
Message=Conversion failed when converting the nvarchar value '('MyDataStringHere')' to data type int.
Number=245
Class=16

The column I am using the parameter against is data type: int. I am not sure if this is the cause of the error. 

My WHERE clause within the NativeQuery looks like this:

WHERE (T3.INTCOLUMN_1 = 1) AND ( T3.INTCOLUMN_2 IN (@Parameter) )

When I manually run this query in SSMS with my copied parameter I have no issues with the data type as either an int value or a text value in the WHERE clause.

Do anyone know how to resolve this error?

Thanks in advance!

-Jonathan

[#2]

@JonathanLewis 

Hi Jonathan,

In such case I'd use something like

MyParameters = "1,2",
Source = Sql.Database("MyServer", "MyDatabase"),
    Query = Value.NativeQuery(Source,"
-- here is SQL body
WHERE (T3.INTCOLUMN_1 = 1) AND ( T3.INTCOLUMN_2 IN (" & MyParameters & ") )
") 

With parameter you pass into the query string as "'1,2'". Perhaps it could be split somehow, not sure.

[#3]

@JonathanLewis , by the way, if your parameters are in supporting table within Excel, let say tblParam with the column Options, you may query your table, change Options to text type if necessary, and

MyParameters = Text.Combine(tblParam[Options], ",")