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

4.12 SQL for Power BI: Views, Joins, CTEs and Where to Transform

By Ravindra Bagale · Last updated: 6 October 2026

Start with a problem

Amir's team argues: should cleaning and totals happen in SQL or in Power BI? Someone created a view that groups by city, and now nobody can split Pune by store.

Explain simply

Use SQL for shared, heavy, row-level rules close to the data (views). Use Power Query for file sources and report-specific steps. Use DAX for totals that must react to filters. Keep the grain you may need later.

Show the data

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

The grouped view threw away order and store detail, so later questions could not be answered. The order-grain view keeps every option open, and DAX totals respond to filters. A SQL running total is fixed when loaded; a DAX running total respects slicers.

Explore further

• CTEs and stored procedures can be used through native SQL, but they usually stop folding; prefer views.

Detailed reference for this lesson:

Use SQL when the database already knows the business rule, the table is large, or several reports must share one definition. Use Power Query when the source is a file, the rule is only for this report, or you cannot create objects in the database.

Joins. A star schema in SQL is the same shape as Module 11. orders joins to product on product_id and to store on store_id. In Power BI you usually do not join them into one flat query. Load the three tables and relate them in the model. A SQL join that flattens everything is how you get a wide, repeating table and a slow model.

Aggregations in SQL. GROUP BY city is useful for a small summary table. If you group away the order id, you cannot count distinct orders later in a new way. Prefer to import the grain you might still filter, and aggregate with DAX measures.

Views. A view is a saved SELECT. Power BI treats it like a table. Good uses: hide unwanted columns, fix a data type, apply a stable filter the business agreed (for example delivered rows only, if that is really the rule). The view stays under the database owner's control.

CTEs (WITH ... AS). A CTE is a named step inside one statement. Power BI does not "call" a CTE by name. You either put the whole statement in a native query (folding usually stops) or, better, save the logic as a view.

Window functions such as SUM(amount) OVER (PARTITION BY customer_id ORDER BY order_date) compute a running total inside the database. That is valid SQL. In Power BI, a running total is usually a measure (Module 13.16), so it still reacts to slicers. A window column is frozen at refresh. Use a window function in a view when the number must be stored per row (for example the previous order date on that row). Use a measure when the user will filter the page.

Stored procedures. Desktop can run some procedures through native SQL (EXEC ...), depending on the source. They often do not fold, they are awkward in DirectQuery, and the Service refresh account must be allowed to execute them. Prefer a view or a table-valued function the connector can read as a table. If a procedure is the only door the DBA gives you, load its result with Import and keep the procedure simple.

Worked example, fictional rows:

-- View the model can import. Grain stays one row per order.
CREATE VIEW dbo.vw_orders_clean AS
SELECT order_id, city, amount, order_date
FROM dbo.orders
WHERE amount IS NOT NULL;

A second view vw_orders_by_city with GROUP BY city would show Pune 1250 and Kolhapur 300, and you could never split Pune by product later. Import vw_orders_clean. Write [Total Sales] = SUM(Orders[amount]) in DAX.

Fix common mistakes

Practise independently

Think like a professional

Ask "which questions might come next?" before aggregating in SQL. Keep the finest grain that the business could ask about.

Ravi Sir's Cards for this lesson

Revision card

मराठी cards