CODEWITHSUNDEEP
sql-serverexcelsql-server-2012powerquery
CodeNinja
CodeNinja

Reputation: 3278

Power Query Assign a string from excel to sql server variable

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 :

enter image description here

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

enter image description here

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

Upvotes: 1

Views: 972

Answers (2)

Alejandro Lopez-Lago - MSFT
Alejandro Lopez-Lago - MSFT

Reputation: 4144

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.

Upvotes: 1

CRAFTY DBA
CRAFTY DBA

Reputation: 14925

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.

enter image description here

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.

enter image description here

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

Upvotes: 2

Related Questions