5. Using variable in Native Query
In most instances, it is easy enough to filter data in query editor, but there are instances where you want to limit data size that's being brought in. I recommend doing it on SQL side by creating View (or prameterized query) . But this isn't feasible when you don't have permission in the database.
When passing parameter to query, you should always take care to convert variable to text type. This is especially important for numeric values. Since, & operator (concatenate) can't be used on non-text data types.
Sample native query to SQL server.
Code:
SELECT *
FROM TableName as t1
WHERE t1.Date >= '8/1/2017'
This will be shown in Advanced query as...
Code:
=Sql.Database("Server:Port", "Dbname", [Query="SELECT * #(lf)FROM TableName as t1 #(lf)WHERE t1.Date >= '8/1/2017'"])
Note that #(lf) represents new line.
Refer to Post #5 above for how to pass variable from worksheet to PowerQuery.
Ex: Where named range vVal holds date value
Code:
mVal = Text.From(Excel.CurrentWorkbook(){[Name="vVal"]}[Content]{0}[Column1]),
Text.From is used to convert to text type.
You can then edit query to...
Code:
=Sql.Database("Server:Port", "Dbname", [Query="SELECT * #(lf)FROM TableName as t1 #(lf)WHERE t1.Date >= '" & mVal & "'"])
One drawback to this method is that editing source by database query window will not be available and you must edit query directly in formula bar or in advanced editor.
Note: You may encounter security level mismatch when variable is used in native query. If that happens, go to "Data source settings" in query editor, and set the database's security and current workbook's security to same level.