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.

Video

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:

Odbc.Query

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:

Syntax

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
FROM sales.fsales
GROUP BY AccountManager, CustomerType
HAVING AccountManager
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.

Click Enter

From the screenshot below, we have successfully retrieved the result from the SQL query:

See you in the next video