Data Transformation Made Simple: 10 Operations in SQL, Pandas, and Power BI DAXArticle · digest for October 21
Three-tier architectural diagram of data processing across SQL, Pandas, and Power BI DAX
Article2 sections≈ 4 min

Data Transformation Made Simple: 10 Operations in SQL, Pandas, and Power BI DAX

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:

  1. Column Selection:
    • SQL: SELECT name, salary FROM employees
    • Pandas: df[["name", "salary"]]
    • DAX: SELECTCOLUMNS(Employees, "name", Employees[name], "salary", Employees[salary])
  2. Row Filtering:
    • SQL: SELECT * FROM sales WHERE amount > 1000
    • Pandas: df[df["amount"] > 1000] or df.query("amount > 1000")
    • DAX: FILTER(Sales, Sales[amount] > 1000) or inside a measure: CALCULATE([Total], Sales[amount] > 1000)
  3. 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)
  4. 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]))
  5. 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) or NATURALINNERJOIN(Orders, Clients)
  6. Distinct Values:
    • SQL: SELECT DISTINCT category FROM products
    • Pandas: df["category"].unique() or df.drop_duplicates(subset=["category"])
    • DAX: DISTINCT(Products[category]) or VALUES(Products[category])
  7. 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")
  8. 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)
  9. 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])))
  10. 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 SUMMARIZECOLUMNS table function

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.

Want a deep diveVotes help us choose what to break down in detail. This material has 0 votes.
Sources · 3