Forum Discussion

BriGoon's avatar
BriGoon
Frequent Visitor
8 years ago
Solved

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

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 ...
Need Help
  • v-jiascu-msft's avatar
    v-jiascu-msft
    8 years ago

    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

v-jiascu-msft's avatar
v-jiascu-msft
Microsoft Employee
8 years ago

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's avatar
BriGoon
Frequent Visitor
7 years ago

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!