📊 Excel → Power BI Cheat Sheet

Every Excel feature you already know has a Power BI equivalent. The table below is your permanent reference.

Browse by formula category — each tab shows the Excel syntax and its DAX equivalent side by side.

=XLOOKUP([ProductID], Products[ProductID], Products[ProductName])
-- Excel: finds ProductName by matching ProductID

-- Power BI DAX equivalent (after creating relationship in Model View):
RELATED(Products[ProductName])
-- DAX: navigates the relationship — no column matching needed

Excel XLOOKUP → Create relationship in Model View → Use RELATED() in calculated column

=VLOOKUP(A2, Table2, 3, FALSE)
-- Excel: brittle column-number approach

-- Power BI: Don't write a formula. Instead:
-- Step 1: Model View → drag CustomerID (Orders) to CustomerID (Customers)
-- Step 2: In DAX column: RELATED(Customers[CustomerName])
-- DAX: relationship-driven — survives column reordering

VLOOKUP → Model Relationship → RELATED()   (never write column numbers in DAX)

=IF([Revenue]>1000, "High", "Low")
-- Excel: simple binary conditional

-- Power BI New Column:
OrderSize = IF(Sales[Revenue] > 1000, "High", "Low")
-- DAX: identical syntax — IF works the same way

Excel IF() → DAX IF()   (syntax is identical — direct 1:1 translation)

=IFS([Region]="East", 0.1, [Region]="West", 0.15, TRUE, 0.05)
-- Excel: multi-branch IFS

-- Power BI DAX equivalent:
Discount = SWITCH(TRUE(),
    Sales[Region] = "East",  0.1,
    Sales[Region] = "West",  0.15,
    0.05)
-- DAX: SWITCH(TRUE(), ...) is the standard multi-branch pattern

Excel IFS() → DAX SWITCH(TRUE(), condition, result, ...)   (more readable for many branches)

=SUMIFS(Sales[Revenue], Sales[Region], "East", Sales[Year], 2024)
-- Excel: sum with multiple conditions

-- Power BI Measure:
East2024Revenue = CALCULATE(
    SUM(Sales[Revenue]),
    Sales[Region] = "East",
    Sales[Year] = 2024
)
-- DAX: CALCULATE modifies filter context — far more dynamic

SUMIFS(range, col1, val1, col2, val2) → CALCULATE(SUM(col), filter1, filter2)

=AVERAGEIF(Sales[Quantity], ">10")
-- Excel: average with one condition

-- Power BI Measure:
AvgLargeOrders = AVERAGEX(
    FILTER(Sales, Sales[Quantity] > 10),
    Sales[Quantity]
)
-- DAX: AVERAGEX iterates a filtered table row-by-row

AVERAGEIF → AVERAGEX(FILTER(table, condition), expression)

=TEXT([OrderDate], "YYYY-MM")
-- Excel: formats a date as "2024-03" string

-- Power BI DAX:
MonthKey = FORMAT(Sales[OrderDate], "YYYY-MM")
-- DAX: FORMAT() is the direct equivalent of TEXT()

Excel TEXT(value, format) → DAX FORMAT(value, format)   (same format strings)

=TRIM([CustomerName])
-- Excel formula: trims leading/trailing spaces

-- In Power Query (M language):
Table.TransformColumns(Source, {"CustomerName", Text.Trim})
-- M: applies Text.Trim to an entire column at once

-- In DAX calculated column:
CleanName = TRIM(Customers[CustomerName])
-- DAX TRIM also removes extra internal spaces

TRIM() works in Excel, Power Query (Text.Trim), and DAX — all three remove leading/trailing spaces

The Power Query interface is identical in Excel and Power BI Desktop. Learn it once, use it everywhere.

Table.Distinct(Source)
-- Removes all duplicate rows from the table

510 rows → 452 rows (duplicate orders removed)

Table.TransformColumns(Source, {{"CustomerID", Text.Trim}})
-- Removes leading and trailing spaces from CustomerID column

" CUST001 " → "CUST001"   (prevents join failures on relationships)

Table.TransformColumnTypes(Source, {{"OrderDate", type date}})
-- Converts OrderDate from text to a proper Date type

Text "2024-03-15" → Date value — enables time intelligence DAX functions

Table.SelectRows(Source, each [Region] = "East")
-- Keeps only rows where Region equals "East"

-- To exclude TEST orders:
Table.SelectRows(Source, each not Text.Contains([Notes], "TEST"))
-- Removes any row where Notes column contains the word TEST

Test rows removed — only real sales data reaches your Power BI model

Table.NestedJoin(
    Orders, {"CustomerID"},        -- left table + key
    Customers, {"CustomerID"},     -- right table + key
    "CustomerData",                -- name of new expanded column
    JoinKind.LeftOuter             -- keep all orders, match where possible
)

Orders table gains CustomerName + Region columns from Customers — same as XLOOKUP, no formula needed

Primary Key vs. Foreign Key

📋 Customers Table (Dimension)
┌─────────────┬──────────────────┐
│ CustomerID  │ CustomerName     │  ← Primary Key (unique, no repeats)
├─────────────┼──────────────────┤
│ CUST0001    │ Acme Corp        │
│ CUST0002    │ TechStart Inc    │
└─────────────┴──────────────────┘

📦 Orders Table (Fact)
┌──────────┬─────────────┬─────────┐
│ OrderID  │ CustomerID  │ Revenue │
├──────────┼─────────────┼─────────┤
│ ORD1001  │ CUST0001    │ $1,250  │  ← Foreign Key (same customer can appear many times)
│ ORD1002  │ CUST0001    │ $890    │
│ ORD1003  │ CUST0002    │ $2,100  │
└──────────┴─────────────┴─────────┘

Customers[CustomerID] (1) ────── (*) Orders[CustomerID]
One customer can have MANY orders → this is a One-to-Many (1:*) relationship

Star Schema (Best Practice) 🌟

1. Get Data — Home → Excel Workbook → Select your named Tables (not the whole file)
2. Transform Data — Power Query Editor opens. Clean, type-correct, and filter. Do not click Load immediately.
3. Model View — Create relationships by dragging Primary Key to Foreign Key across tables
4. Report View — Drag fields to create visuals. Same logic as PivotTable rows/values/columns.
5. Publish — Power BI Service → Share dashboards with your team and set scheduled refresh

Suggested Student Workflow

1. Open Sales_Raw_Messy.csv in Office.com Excel and convert to Table → Name it tbl_Sales_Raw
2. Open Power Query: Data → From Table/Range
3. Remove duplicate rows
4. Trim CustomerID and Notes columns to remove whitespace
5. Standardize Region to Title Case (Transform → Format → Capitalize Each Word)
6. Filter out rows where Notes contains "TEST"
7. Remove rows with blank CustomerID or blank OrderDate
8. Close & Load To… → Table → Name output tbl_Sales_Clean
9. Build a PivotTable: Revenue by Region and Product Category
10. Compare row count and totals to Sales_Clean_Reference.csv

Click each card to reveal the Power BI equivalent.

6 questions covering formulas, Power Query, and data modeling.