Power Query Assign a string from excel to sql server variable

Question

I am using Power query & I would like to assign a comma delimited string from an excel cell to a sql server variable. The power query I have so far is below. My Parameter is in a Excel "Table3" in "Column2" :

let

    ProdParameterSource =Excel.CurrentWorkbook(){[Name="Table3"]}[Content]{0}[Column2],


    Source = Sql.Database("server", "database", [Query="Declare @Product varchar(max) = "&(ProdParameterSource)&"  select @Product"])
in
    Source

I see the below error :

The parameters I am using as seen in the excel sheet are below :

How can I fix this error and see my input parameters in the sql server variable ABC as '44,216' (with the inverted comma).

Answer 1

I do not do a lot of power query but I do work in Power BI Desktop version.

First, you need to look at the reference to the SQL.Database() on MSDN.

It is looking for a whole query, either a dynamic string you make up or a call to a stored procedure.

My simple example below, pulls data from the Adventure Works DW 2012 Customer table.

What you are missing is a TSQL statement or multiple TSQL statements. Use the semicolon to combine statements into one batch to be called.

The example below create a variable @x as an integer and returns the value. This is almost like you example above.

You will be prompted for security (trusted credentials) and it will tell you that it is an unencrypted connection.

I did some looking around the NET. This article from Reeves Smith is like my last example but slanted towards power query and excel. Just remember, both products use the same engine.

https://reevessmith.wordpress.com/2014/08/19/power-query-and-stored-procedures-with-parameters/

Again

Answer 2

This will fix it: [Query="Declare @Product varchar(max) = '" & ProdParameterSource & "' select @Product"]).

This will fix it in a safer way, since you will escape any extra single-quotes which could break out of the string value: [Query="Declare @Product varchar(max) = '" & Text.Replace(ProdParameterSource, "'", "''") &"' select @Product"]).

What happened is that Power Query treats the text passed to Query as the entire script, and when you built the script you didn't put the value of ProdParameterSource in quotes, so the script appears to set a varchar value to 44,216 (without quotes). This is an invalid statement.