SSMS-SQL-PowerQueryM-Functions

Using Power Query M to extract values from Excel workbooks for dynamic insertion into SQL code.

Requirements

Ensure you have all the requirements and everything is configured properly (especially SSMS and the ODBC Driver).
Open an Excel Workbook.
Name you're first sheet as Parameters (this is where we will be extracting the text value from).
Paste your SSMS server name wherever you'd like (ideally on the top left corner).
Have another cell ready below the server name for continents, and name the cells 'Server' and 'Continent', respectively.
Now go to the Data Tab ➡️ Get Data ➡️ From Other Sources ➡️ Blank Query ➡️ Advanced Editor.
Once you're in the Advanced Editor you will need to first input the fnGetParam Power Query M script I've attached, this code basically extracts the value from the named cells (make sure the it is named fnGetParam or else the function call will not work).
Create another blank query either by right-clicking in the [Queries] section and selecting New Query.
Paste the SQL code I've attached and change all the necessary customizable named components such as the ODBC Driver you're using and the database you're extracting from.
Voila, you can now close and load, and if it asks you for authentication, I usually select Windows ➡️ Use Current Credentials.

Name		Name	Last commit message	Last commit date
Latest commit History 1 Commit
README.md		README.md
SQL Query Script.txt		SQL Query Script.txt
Sample.xlsx		Sample.xlsx
fnGetParam.mp4		fnGetParam.mp4
fnGetParam.txt		fnGetParam.txt