Anyone who works with data eventually hits a mild bout of cognitive dissonance. In the morning you write a declarative SQL query against a PostgreSQL warehouse, by afternoon you are cleaning an export in Python with Pandas, and by evening you are building an interactive Power BI dashboard and wrestling with DAX formulas.
Even though the underlying math never changes, the syntax across these three environments diverges enough to scramble your brain. I put together a clear cheat sheet of ten fundamental data wrangling operations so you can switch mental gears effortlessly between the database, your Python code, and the BI analytics layer.
The Core Ten: From Column Selection to Pivot Tables
Let's walk through how the exact same computational task looks across all three dialects:
- Column Selection:
- SQL:
SELECT name, salary FROM employees - Pandas:
df[["name", "salary"]] - DAX:
SELECTCOLUMNS(Employees, "name", Employees[name], "salary", Employees[salary])
- SQL:
- Row Filtering:
- SQL:
SELECT * FROM sales WHERE amount > 1000 - Pandas:
df[df["amount"] > 1000]ordf.query("amount > 1000") - DAX:
FILTER(Sales, Sales[amount] > 1000)or inside a measure:CALCULATE([Total], Sales[amount] > 1000)
- SQL:
- Sorting:
- SQL:
SELECT * FROM orders ORDER BY created_at DESC - Pandas:
df.sort_values(by="created_at", ascending=False) - DAX:
TOPN(10, Orders, Orders[created_at], DESC)(or configured via sort order in Power BI Desktop)
- SQL:
- Aggregation and Grouping:
- SQL:
SELECT dept, SUM(salary) AS total FROM staff GROUP BY dept - Pandas:
df.groupby("dept")["salary"].sum().reset_index() - DAX:
SUMMARIZE(Staff, Staff[dept], "total", SUM(Staff[salary]))
- SQL:
- Joining Tables:
- SQL:
SELECT * FROM orders o INNER JOIN clients c ON o.client_id = c.id - Pandas:
pd.merge(orders, clients, on="client_id", how="inner") - DAX:
RELATED(Clients[name])(via a model relationship) orNATURALINNERJOIN(Orders, Clients)
- SQL:
- Distinct Values:
- SQL:
SELECT DISTINCT category FROM products - Pandas:
df["category"].unique()ordf.drop_duplicates(subset=["category"]) - DAX:
DISTINCT(Products[category])orVALUES(Products[category])
- SQL:
- Handling Missing Values (Coalesce / Imputation):
- SQL:
SELECT COALESCE(phone, "no data") FROM users - Pandas:
df["phone"].fillna("no data") - DAX:
COALESCE(Users[phone], "no data")
- SQL:
- Ranking:
- SQL:
RANK() OVER (PARTITION BY dept ORDER BY salary DESC) - Pandas:
df.groupby("dept")["salary"].rank(ascending=False, method="min") - DAX:
RANKX(ALL(Staff), [Total Salary], , DESC)
- SQL:
- Cumulative Sum (Running Total):
- SQL:
SUM(amount) OVER (ORDER BY date ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) - Pandas:
df.sort_values("date")["amount"].cumsum() - DAX:
CALCULATE(SUM(Sales[amount]), FILTER(ALL(Sales), Sales[date] <= MAX(Sales[date])))
- SQL:
- Pivot Tables (Cross-tabulation):
- SQL: conditional aggregation
SUM(CASE WHEN year = 2026 THEN amount ELSE 0 END) GROUP BY dept - Pandas:
df.pivot_table(index="dept", columns="year", values="amount", aggfunc="sum") - DAX: a Matrix visual or the
SUMMARIZECOLUMNStable function
- SQL: conditional aggregation
Architectural Separation: Where to Compute What
To keep your pipeline from crashing with out-of-memory errors, keep a clean division of labor across all three layers:
-- 1. Database layer: filter and aggregate data at the source
SELECT department, DATE_TRUNC('month', order_date) AS month, SUM(revenue) AS total_revenue
FROM enterprise_orders
WHERE status = 'completed' AND order_date >= '2026-01-01'
GROUP BY 1, 2;
# 2. Python/Pandas layer: complex cleaning and vectorized transformations
import pandas as pd
def clean_and_accumulate(df: pd.DataFrame) -> pd.DataFrame:
df["total_revenue"] = df["total_revenue"].fillna(0)
df = df.sort_values(["department", "month"])
df["running_total"] = df.groupby("department")["total_revenue"].cumsum()
return df
The rule of thumb is straightforward: always push initial coarse filtering and table joins down to the database in SQL—it saves network bandwidth and RAM. Hand vectorized cleaning, validation, and enrichment over to Pandas. And leave dynamic slicing, filter context, and visual ratios to Power BI's VertiPaq engine and DAX formulas.
We previously compared Excel logic against Python and SQL code, and this side-by-side reference will help you translate any analytical idea straight into the right tool's syntax.

