4. Connecting to Databases: SQL Server, MySQL, DirectQuery and Live Connection

4.3 Advanced Options and Native SQL Queries

By Ravindra Bagale · Last updated: 6 October 2026

Start with a problem

Zoya needs order lines for Pune and Nashik with store names. She writes a SQL statement but is unsure whether Power BI can use it.

Explain simply

Advanced options › SQL statement runs your own SQL instead of picking tables. It works, but later Power Query steps usually stop folding, so keep the SQL simple and list columns explicitly.

Show the data

About this data: This table uses columns: order_id, order_date, store_id, platform, category, amount, delivery_mins, status. This table uses columns: order_id, store_name, city, amount. This table uses columns: order_date, city, orders, sales. Values shown are the lesson examples already on this page (not a new dataset).

Fictional sample data. Practice file: quickcommerce_practice.sql (source: src/practice/).

Demonstrate

Show the result

Your result should look like this (fictional sample):

Explain what happened

SQL Server ran each statement and returned only the result. The join added store names; the WHERE kept 6 of 8 orders (Pune 850 + Nashik 800 = 1,650). The GROUP BY gave one row per date and city.

Explore further

• Prefer a database view for shared logic; Power BI can then fold steps on top of it.

Detailed reference for this lesson:

Clicking Advanced options in the SQL Server dialog shows:

Example 1 – order lines for our six Maharashtra cities (JOIN + WHERE):

SELECT  o.OrderID, o.OrderDateTime, o.ProductID, o.Quantity, o.Amount,
        o.DeliveryTimeMins, o.OrderStatus, o.Platform,
        s.StoreID, s.StoreName, s.Area, s.City
FROM    dbo.Orders    AS o
INNER JOIN dbo.DarkStore AS s ON s.StoreID = o.StoreID
WHERE   s.City IN ('Pune', 'Mumbai', 'Nashik', 'Sambhaji Nagar', 'Kolhapur', 'Nagpur')
  AND   o.OrderDateTime >= '2025-01-01';

Example 2 – daily summary per city and platform (GROUP BY):

SELECT  CAST(o.OrderDateTime AS date)   AS OrderDate,
        s.City, o.Platform,
        COUNT(DISTINCT o.OrderID)       AS Orders,
        SUM(o.Amount)                   AS Sales,
        AVG(CAST(o.DeliveryTimeMins AS decimal(10,2))) AS AvgDeliveryMins
FROM    dbo.Orders o
JOIN    dbo.DarkStore s ON s.StoreID = o.StoreID
WHERE   o.OrderStatus = 'Delivered'
GROUP BY CAST(o.OrderDateTime AS date), s.City, o.Platform;

Fix common mistakes

Practise independently

Think like a professional

Keep business logic in views owned by the database team, and keep native SQL in Power BI short and documented.

Ravi Sir's Cards for this lesson

Revision cardInterview cardComparison card

मराठी cards