Excel Power Query - How to use Dynamic Value in SQL Statement

[BriGoon]

Hello All,

I have an excel spreadsheet. I want to pull some data from a SQL database and display it in the spreadsheet. I also have a cell in the spreadsheet. I would like to have a user enter a value in that feild and then use that value in the where clasue of my SQL statement. 

If I hard code the value this works: 

let

    Source = Sql.Database("DatabaseServer", "DatabaseName", [Query=("SELECT *#(lf)FROM [Server].[database].[dbo].item as i#(lf)LEFT JOIN [server].[database].[dbo].itemwhse as iw on i.item = iw.item#(lf)LEFT JOIN [server].[database].[dbo].commodity as cc on i.comm_code = cc.comm_code#(lf)Where i.item Like '0013%'")])
in
    Source

If I add the code to pull the cell value and place it into the SQL statement like this: 

let
    Source1 = Excel.CurrentWorkbook(){[Name="itemFilter"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source1,{{"item", type text}}),
    Source = Sql.Database("DatabaseServer", "DatabaseName", [Query=("SELECT *#(lf)FROM [Server].[database].[dbo].item as i#(lf)LEFT JOIN [server].[database].[dbo].itemwhse as iw on i.item = iw.item#(lf)LEFT JOIN [server].[database].[dbo].commodity as cc on i.comm_code = cc.comm_code#(lf)Where i.item Like '" & #"Changed Type" & "'")])
in
    Source

I get this error: 

Expression.Error: We cannot apply operator & to types Text and Table.
Details:
    Operator=&
    Left=SELECT *
FROM [server].[database].[dbo].item as i
LEFT JOIN [server].[database].[dbo].itemwhse as iw on i.item = iw.item
LEFT JOIN [server].[database].[dbo].commodity as cc on i.comm_code = cc.comm_code
Where i.item Like '
    Right=Table

How do I convert the value I pulled from the cell to text? (I assume Power Query is unable to do the type converion just using the & operator?)

[Anonymous]

Hi, 

@BriGoon  and @v-jiascu-msft 

I'm pretty new here and I'm trying to do the same thing. Is the hard code written in the Get data>from database>from oracle database ?

Is this where you're entering the code? Also, where are we referring the cell address of where the user is inputting the data?? How do you refer to that cell address in the code?

Please help.

Regards,

Smith

[v-jiascu-msft · Accepted solution]

Hi @BriGoon,

The first two steps return a table. Though there could be only one value, that can't be used as a text string. Please try it out like below.

let
    Source1 = Excel.CurrentWorkbook(){[Name="itemFilter"]}[Content],
    CustomValue = Source1{0}[item],
    Source = Sql.Database("DatabaseServer", "DatabaseName", [Query=("SELECT *#(lf)FROM [Server].[database].[dbo].item as i#(lf)LEFT JOIN [server].[database].[dbo].itemwhse as iw on i.item = iw.item#(lf)LEFT JOIN [server].[database].[dbo].commodity as cc on i.comm_code = cc.comm_code#(lf)Where i.item Like '" & Text.From(CustomValue)  & "'")])
in
    Source

Best Regards,

Dale

[BriGoon]

Thanks, that is what I thought might be happening but was not sure how to access a single item. 

If anyone is interested, I ended up building a function called GetValue() to get the values (I needed to do this for multiple sheets/queries)

(rangeName) =>
    Excel.CurrentWorkbook(){[Name=rangeName]}[Content]{0}[Column1]

I then just assing a name to the cell and call the function to get the value: 

varName = Text.From(GetValue("cellName"))

Then I can use the variable in th Query concatination. 

Thanks Dale!