Welcome to Excel Jet Consult blog post. In this video, I demonstrated how to write advanced SQL Query in Power Query of Excel using ODBC.Query M Function.
Set Up ODBC Connectiviity
For this operation, we are required to set up the ODBC driver on our local machine. To that, you can watch this video below:
The ODBC.QUERY Returns the result of running
query with the connection string
connectionString using ODBC.
connectionString can be text or a record of property value pairs. Property values can either be text or number. An optional record parameter,
options, may be provided to specify additional properties. The record can contain the following fields:
Odbc.Query(connectionString as any, query as text, optional options as nullable record) as table
We need to load a dummy table into the Power Query which we are going to modify to write the SQL query using the ODBC.Query M function.
The screenshot of the data above shows that the dummy table is formatted as an Excel table.
using CTRL + T.
To load the table into the Power Query, click on From Table/Range in the Get & Transform Data
group of the Data tab.
In the Applied Steps, delete the Changed Type.
Enter the M Code below into the Power Query formula bar.
= Odbc.Query(“dsn=mySQL”,”SELECT AccountManager,
CustomerType, SUM(Total) as TotalSales, AVG(Total) as Average_Total_Sales
GROUP BY AccountManager, CustomerType
IN (‘Abiola David’,’Connor Rulibeth’)
ORDER BY AccountManager;”)
The above SQL Query will return the Total Sales and Average Total for Account Manager named Abiola David and Connor Rulibeth and the corresponding Customer Type.
From the screenshot below, we have successfully retrieved the result from the SQL query:
See you in the next video