Passing parameters to Native SQL Query in Excel and Power BI

Using Power Query in Excel you can parameterize a native query to SQL server or Oracle. While this is not something new, it’s very useful as with some simple data validation you can turn your Excel file into a front-end query tool. Here are four simple steps to follow:
Step 1a) Write the query code & test it;

Step 1b) Paste it into the “SQL Statement” area when connecting, opening the Advanced Options dialogue; Enter your credentials and test it again to make sure it works;

Step 2) Once the query loads, go into the Advanced Editor and split it at the Where clause, so you can insert a reference to another query, subject to user input (beware of the FormulaFirewall error, a link to Ken Puls’ solution of this problem below).
For example: “Select * from db where column1=”&”x“&”order by Column2”). At this point it may be a good idea to have a backup query that you can check if the steps below don’t return the expected result;
Step 3) In place of the X, use the name of the query, containing the parameter or the name of step. What I mean is that the steps, although in one query in PQ, don’t need to necessarily reference the one before that, have a look at the example below:

Step 4) Make sure that the Output format of the M code is aligned with the script (i.e. date format is dd/mm/yyyy in both places)

The original article I based this post on is by Chris Webb: https://blog.crossjoin.co.uk/2016/12/11/passing-parameters-to-sql-queries-with-value-nativequery-in-power-query-and-power-bi/

The article by Ken Puls about Formula.Firewall error can be found here: https://www.excelguru.ca/blog/2015/03/11/power-query-errors-please-rebuild-this-data-combination/

I hope that would be useful for you.

Have a great day ahead!

Vitali