Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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?)
Solved! Go to 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
Hi,
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
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
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!
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
105 | |
101 | |
81 | |
79 | |
67 |
User | Count |
---|---|
122 | |
110 | |
94 | |
82 | |
77 |