🚀 Skill Academy: Zero → World-Class Expert — START HERE

A personal academy with growing umbrellas: 🎓 Business Analytics — 9 complete tracks (Power BI, Excel, Power Query, SQL, Tableau, Python for Finance, Statistics for Finance, Data Visualization, Time Series & Forecasting) — and 🗣️ English — Basic English foundations, Business News Speaking (headlines to earnings briefs) and Business Vocabulary (office words to finance fluency). Written for a student starting from absolute zero. Follow the order. Do every lab. That's the whole secret.

1. What you will become

2. Setup — do this TODAY (30 minutes)

1. Power BI Desktop (free, Windows only): install from the Microsoft Store (search "Power BI Desktop") — it auto-updates monthly. No Windows PC? Use a Windows VM (Parallels on Mac) or a cloud Windows PC. There is no native Mac/Linux version.
2. Power BI Service account: go to app.powerbi.com. It requires a work or school email. No work email? Create a free Microsoft 365 E5 developer tenant or start a Microsoft Fabric free trial to get one.
3. Sign in in Power BI Desktop (top-right) with the same account.
4. Get the practice data: already waiting for you in PowerBI-Zero-to-Expert/datasets/ (details below).
5. Optional power tools (install when you reach Module 8): DAX Studio, Tabular Editor 2, Bravo — all free.

3. Course map

🆕 Now with a full EXCEL TRACK (same ShopKart data): Modules E1–E5 take you from your first spreadsheet to Power Query, dynamic arrays and macros — the ideal companion to Power BI. They share the same engine!

4. Your 12-week plan (8–10 hrs/week)

Daily rhythm (90 min): 25 min learn the lesson → 55 min build/practice → 10 min write 3 lines in your learning journal: what clicked, what broke, what I'd explain to a friend.

5. The 7 Laws of Mastery (read weekly)

1. 30/70 rule — learn 30%, build 70%. Watching videos never made anyone an expert.
2. Do every lab twice — once following along, once from a blank file.
3. Never paste DAX you can't explain line by line out loud.
4. Portfolio from day one — screenshot every project; you'll publish them in Module 09.
5. One deep article per week — SQLBI.com is the gold standard; start after Module 4.
6. Teach to learn — explain each new concept to someone (or to me). If you can't explain it simply, redo the lesson (Feynman technique).
7. Stuck > 30 minutes? Use the 4-step rescue: ① re-read the exact error, ② reproduce it in a tiny test page, ③ simplify (smaller table, simpler measure), ④ still stuck — ask me and paste your formula + model screenshot description.

6. Your practice datasets (in datasets/)

All revenue numbers come from a synthetic Indian retail company, "ShopKart India". Money is ₹ INR.

7. How each module works

🎯 Objectives → 📘 Concepts (in plain English) → 🧪 Lab (click-by-click) → 💪 Exercises → ❓ Quiz → ✅ Answers → Mastery checklist. Try every exercise before opening the answers. Struggle is where the learning happens.

8. After the course: the expert treadmill

• Weekly: one Workout-Wednesday style challenge rebuild; one SQLBI article.
• Monthly: one new dataset from data.gov.in / Kaggle / Our World in Data — model it, publish it.
• Always: answer forum questions (community.fabric.microsoft.com). Teaching is the final level of mastery.
• Get certified: PL-300 (see Resources file for the exact study map).

➡️ Next: open Module_01_Foundations.md. See you in Lesson 1.

— Your tutor

Module 01 — Power BI Foundations & Your First Report

🎯 Objectives

By the end you will: explain what BI is and where Power BI fits, install and navigate Power BI Desktop, understand the 5-step workflow, and build and save your first real report using Sales.csv.

📘 1.1 What is Business Intelligence, really?

A shop owner asks: "Which products actually make me money? Which city buys most? Are we better than last year?" The answers are buried in spreadsheets. Business Intelligence (BI) is the discipline of turning raw data into answers a human can act on. A BI tool does four jobs: Get data → Store/model it → Analyze it → Show it.

Power BI is Microsoft's BI platform and the market leader (Gartner has ranked it #1 in Analytics & BI for years). Companies hire for it because it's everywhere: startups, banks, hospitals, the IPL, governments.

📘 1.2 The Power BI ecosystem

Licensing in one breath: Power BI Desktop is free. A Free license lets you publish to your personal "My workspace". Pro (≈$14/user/mo, included in Microsoft 365 E5) is the standard for sharing with colleagues; Premium Per User (≈$24) adds large-model/AI features; Fabric capacity licenses cover big organizations. You can complete this entire course on free licenses.

📘 1.3 Install & tour

1. Microsoft Store → install Power BI Desktop → open it.
2. Close the splash screen. The app opens on the Report view.

The mental map (learn these 8 landmarks):

1. Three views (left edge): 📊 Report · 🗒 Table · 🔗 Model — plus DAX query view for writing queries.
2. Ribbon (top): like Office. Key tabs: Home (Get Data, Enter Data, Refresh, New measure), Insert (visuals, buttons, shapes), Modeling (relationships, calculations), View (themes, panes), Optimize (Performance Analyzer).
3. Data/Fields pane (right): your tables and columns.
4. Visualizations pane: the chart gallery + the Build visual wells (X-axis, Y-axis, Legend…) and the Format paintbrush.
5. Filters pane: filters for this visual / this page / all pages.
6. Canvas: the page you design on.
7. Page tabs (bottom): like Excel sheets — a report has pages.
8. Power Query Editor: Home → Transform data opens a whole second app for cleaning data (Module 02).

📘 1.4 The 5-step workflow (memorize this)

GET DATA  →  CLEAN (Power Query)  →  MODEL (tables + relationships)  →  VISUALIZE  →  SHARE
   CSV/DB/web        Module 02               Module 03                 Modules 4–6     Module 07

Every professional Power BI project follows this loop — often repeatedly.

🧪 LAB 1 — Build your first report (45 min)

Goal: a one-page report of ShopKart India sales volume. Don't aim for pretty — aim for finished.

1. Get data: Open Desktop → Get Data → Text/CSV → select datasets/Sales.csv → preview window → click Load (not Transform yet).

Power BI shows "Applying query changes" — it's importing 1,300 rows into memory.

2. Inspect: Click Table view (left edge). See the 7 columns. Note how Power BI guessed data types (123 = whole number, 📅 = date, Σ = numeric).
3. Meet the Fields pane: back in Report view, expand the Sales table on the right. These are your building blocks.
4. Visual A — KPI card: click empty canvas → in Visualizations pane click the Card icon → drag Quantity into Fields. It shows Sum of Quantity. Every number dragged in gets an aggregation (Sum here) — click the dropdown arrow on the field in the well to change it.
5. Visual B — Column chart: empty canvas → Clustered column chart → X-axis: CustomerID → Y-axis: Quantity. Too crowded? Good — that's a lesson; we'll fix in the exercises.
6. Visual C — Donut chart: Legend: Discount → Values: Quantity. See how discount buckets share the volume.
7. Format: select a visual → paintbrush icon (Format) → General → Title on, type "Units by Customer"; Visual → Data labels on. Set page size: click empty canvas → Format → Canvas settings → 16:9.
8. Save: Module01_FirstReport.pbix. A .pbix file = data + model + report, all in one.

💡 What just happened conceptually: You loaded a table (step 1 of the workflow) and built implicit measures (Power BI auto-summing). In Module 4 you'll outlaw implicit measures and write explicit DAX — one of the biggest beginner-to-pro jumps.

💪 Exercises (do all 5)

1. Change Visual B to a bar chart, then sort it descending (⋯ menu on the visual → Sort axis).
2. Visual B problem: 30 customers don't fit. Use the Filters pane → CustomerID → Top N → show Top 10 by Sum of Quantity.
3. Add a second page (bottom +), put a line chart there: X = OrderDate, Y = Sum of Quantity. Drill: use the date axis arrows to move Year → Quarter → Month.
4. Add UnitPrice to the donut's Tooltips well — hover and confirm it appears in the hover card.
5. In the Fields pane, right-click Quantity → Rename → Units Sold. Notice visuals update everywhere. (In Module 3 we rename properly in the model.)

❓ Quiz

1. Name the 5-step Power BI workflow in order.
2. Which license do you need to build reports in Power BI Desktop?
3. What's the difference between Report view and Table view?
4. True/False: a .pbix stores only the report layout, not the data.
5. What is an implicit measure and why will we replace it later?
6. Where do you set a filter that applies to all pages of a report?

✅ Answers

1. Get Data → Clean (Power Query) → Model → Visualize → Share.
2. None — it's completely free (you only need licenses for sharing in the Service).
3. Report view = the canvas where you design pages; Table view = spreadsheet-style peek at the loaded data for a selected table.
4. False — an Import-mode .pbix embeds a compressed copy of the data + model + report.
5. An auto-aggregation Power BI creates when you drag a numeric column in (e.g., Sum of Quantity). We replace it with explicit measures (DAX) for control, reusability, and correctness.
6. The Filters on all pages section of the Filters pane.

✅ Mastery checklist

• I can name the ecosystem pieces and the 5-step workflow
• I can load a CSV and build card, column, donut, and line visuals
• I can format titles/labels and save a .pbix
• I redid Lab 1 from a blank file without looking

Next: Module_02_Power_Query.md — where raw data meets its maker.

Module 02 — Power Query: Get & Transform Data Like a Pro

80% of analytics time is data cleaning. Power Query is the machine that gives you that time back. Experts don't clean data by hand — they build repeatable recipes.

🎯 Objectives

Connect to files, databases and web sources · master the 25 most-used transformations · understand Applied Steps and the M language · clean a genuinely filthy dataset · merge/append/unpivot with confidence.

📘 2.1 What Power Query is

Power Query = ETL engine (Extract, Transform, Load) built into Power BI (and Excel). You connect to a source, shape the data through a UI, and Power Query records every move as an Applied Step. Hit Refresh and it re-runs the whole recipe on new data automatically. This idea — recipes, not manual edits — separates pros from spreadsheet janitors.

Open it: Home → Transform data. It has its own window with: Ribbon (transform tools), Queries pane (left, your tables), Query Settings → Applied Steps (right, your recipe), Formula bar (the M code of the selected step).

📘 2.2 Connecting to data ("Get Data")

When you connect you choose Load (skip cleaning) or Transform Data (open Power Query). Default habit of experts: Transform Data — always at least verify types.

📘 2.3 The 25 transformations you'll use weekly

Join kinds in Merge (memorize 3, know 6): Left Outer (keep all left + matches — the default, use 90% of the time) · Inner (only matches) · Full Outer (everything, nulls where unmatched) · Right Outer · Left/Right Anti (rows with no match — brilliant for quality checks). After merging, expand the new column's ⇆ icon to pick which columns to bring in.

📊 Data profiling (turn it on!)

View tab → Column quality (valid/error/empty %), Column distribution (distinct/unique), Column profile. Experts glance at these before doing anything else.

📘 2.4 Applied Steps & the M language

Every click = a step; every step = one line of M code:

let
    Source      = Csv.Document(File.Contents("C:\...\Messy_Sales_Raw.csv"), ...),
    Headers     = Table.PromoteHeaders(Source),
    Trimmed     = Table.TransformColumns(Headers, {{"Product", Text.Trim}}),
    TypedRows   = Table.TransformColumnTypes(Trimmed, {{"Qty", Int64.Type}}),
    NoDupes     = Table.Distinct(TypedRows, {"OrderNo"}),
    NoErrors    = Table.RemoveRowsWithErrors(NoDupes)
in
    NoErrors

M survival kit:

• let … in … — steps are named; in returns the final one. Steps reference previous step names — that's how the pipeline flows.
• #"Step Name With Spaces" — quoted identifiers.
• each _ = shorthand for "for every row, current row". Text.Upper([Region]) inside Table.TransformColumns transforms a column.
• Errors: try [Qty] otherwise 1 → default value instead of error.
• Right-click a step: Rename it like a professional (Fix Product Case, not Changed Type1), Delete it, or Insert after. Deleting a step is how you undo. Steps can be reordered (drag), and clicking any step shows the data at that moment — your built-in debugger.
• Advanced Editor (Home/View) shows full code. Copy a query's M into a text file = documentation.

Query management the pro way (staging pattern):

• Name queries properly: stg_Sales_Raw (staging, don't load), Sales (final, load).
• Right-click a query → uncheck Enable load for staging/intermediate queries → they refresh but don't bloat the model. Keep Include in report refresh on.
• Reference (right-click → Reference) = new query starting from this one (chain). Duplicate = independent copy. Use Reference almost always.

📘 2.5 Combine files from a folder (preview of pro life)

Get Data → Folder → point at a folder of monthly CSVs → Combine & Transform → Power Query builds a sample query + function and applies it to every file automatically, adding a Source.Name column. Filter that column to exclude temp files (~$*). New file dropped next month → Refresh and it flows in. (Combine = module 8 refresher too.)

🧪 LAB 2 — Rescue Messy_Sales_Raw.csv (60–75 min)

Situation: A legacy system exported garbage. Leadership wants Qty & Price by Region, monthly. Your job: a repeatable cleaning recipe.

1. Home → Transform data → New Source → Text/CSV → datasets/Messy_Sales_Raw.csv → OK. Note Power BI auto-added Promoted Headers + Changed Type steps.
2. Profile first: View → check Column quality/distribution. See the errors & empties appear under each column header.
3. Clean Product names: select Product → Transform → Format → Trim, then Format → Capitalize Each Word. (Whitespace + lowercase bugs were breaking any future join to Products.)
4. Fix Region chaos: select Region → Trim first (kills " West"). Then Replace Values: north→North, NORTH→North, repeat for south/east/west variants. (Shortcut the experts use: Format → Capitalize Each Word does all of it in one step — try it and delete your replace steps!)
5. Fix Price: right-click Price → Replace Values: ₹ → (leave Replace With empty). Then Replace , → empty. Now Transform → Data Type → Whole Number. Watch Errors appear where rows said N/A/error:Home → Remove Rows → Remove Errors.
6. Fix Qty: Data Type → Whole Number (nulls & N/A become errors). We keep blanks-as-1 for empty, drop N/A: first Replace Values N/A→1 before changing type, or replace errors afterward. Choose one; justify it in your journal.
7. Fix the dates — the classic trap: Order Date has both 2024-10-08 and 25-10-2024. Correct method with mixed formats in one column is Change Type → Using Locale won't fully fix it; the robust pattern: Split the formats — add a Conditional Column flagging length, or simplest for this dataset: right-click Order Date → Change Type → Using Locale → Date, locale English (United Kingdom) handles dd-MM-yyyy; ISO strings still parse. Verify with column profile (Valid = 100%).
8. Kill duplicates: select OrderNo → Home → Remove Rows → Remove Duplicates. (148 → 140 rows.)
9. Parse OrderNo: Split Column → By Delimiter - → keep part 2, type Whole Number. Rename OrderID.
10. Add a Revenue column: Add Column → Custom Column: [Qty] * [Price] → name LineTotal.
11. Group it like a boss: (optional stretch) Group By → Region, new column Revenue = Sum of LineTotal. Undo it after inspecting — we want row-level data in the model; aggregation belongs in DAX (Module 4).
12. Rename the query stg_MessySales_Clean, rename steps meaningfully, then Close & Apply.

Expected end state: ~140 clean rows, Order Date = Date, Qty/Price/LineTotal = numbers, Region ∈ {North, South, East, West}.

💪 Exercises

1. Add a custom column PriceBand: if [Price] >= 15000 then "High" else if [Price] >= 3000 then "Mid" else "Low" (conditional column UI or custom column — do both).
2. Use Column From Examples on Product to extract the first word only. Inspect the M it generated.
3. Customers.csv: load it, split nothing, but create column YearsAsCustomer = number of full years between JoinDate and today. (Hint: Add Column → Date → Age, then Date → Total Years; or custom DateTime.Date(DateTime.LocalNow()).)
4. Load Products.csv; Merge it into your clean messy table on product name; expand Category. How many rows failed to match, and why might that happen in real life?
5. Append Sales.csv to itself (reference → keep 10 rows) — observe row count doubles. Then delete the step chain and explain Append vs Merge to your journal.
6. Load an Excel-style problem: type this in Enter Data, then unpivot it to rows (Month | Sales):

Region: North, South | Jan: 100,120 | Feb: 90,140 | Mar: 130,110

7. Build a parameter (Manage Parameters): pSourceFolder (text, the datasets folder path). Advanced: rewrite your CSV source step to use it via Advanced Editor.
8. Challenge: modify the messy-clean query so any new unseen mess (e.g., € symbols) doesn't break refresh — use try … otherwise in a custom cleaning column.

✅ Selected answers

• Ex 1 M: if [Price] >= 15000 then "High" else if [Price] >= 3000 then "Mid" else "Low" — M if expressions are exactly this; note lowercase if/then/else.
• Ex 6 unpivot: select Region column → right-click → Unpivot Other Columns → rename Attribute→Month, Value→Sales.
• Ex 4: near-zero mismatches after trimming/case fix; in real life joins fail due to whitespace, case, spelling variants, and type mismatches (text "10018" vs number 10018).

❓ Quiz

1. Why is "Applied Steps" such a big deal — what happens on Refresh?
2. Merge vs Append — one line each.
3. Which join kind finds customers with no orders?
4. Unpivot solves what shape problem? Draw before/after.
5. What is a staging query and should it Load?
6. What does each mean in M?
7. Your column shows Error after a type change — give two fix strategies.

✅ Answers

1. Every applied step re-runs top-to-bottom on new data: cleaning becomes automatic and repeatable; nothing is done by hand twice.
2. Merge = join tables side-by-side on a key (adds columns). Append = stack tables vertically (adds rows).
3. Left Anti Join from Customers→Sales.
4. Wide → tall. Before: one column per month (Jan|Feb|Mar…). After: one Month column + one Value column — which models and DAX need.
5. An intermediate query used only to build others; load disabled, refresh enabled — keeps the model lean.
6. "For each row" — _ is that row, e.g., each [Qty] * [Price].
7. ① Fix the underlying text first (replace invalid tokens) then convert; ② convert, then Remove Errors (or Replace Errors with a default) — choosing is a business decision (keep vs drop bad data), which is why data profiles matter.

✅ Mastery checklist

• I can clean a messy file end-to-end without touching the original
• I can read & lightly edit the M in Advanced Editor
• I use staging queries with load disabled
• I can Merge (with the right join) and Append on command
• I redid Lab 2 from scratch and got close to the same steps

Next: Module_03_Data_Modeling.md — the star schema, aka why your DAX will be easy.

Module 03 — Data Modeling: The Star Schema (Where Experts Are Made)

Mediocre analysts fight DAX all day. Experts win before writing a single measure — because their model makes DAX simple.

🎯 Objectives

Facts vs dimensions · star vs snowflake · relationships (cardinality, cross-filter, active/inactive) · the mandatory Date table · model hygiene (hide keys, sort-by, categories, hierarchies) · build the ShopKart star schema you will use for the rest of the course.

📘 3.1 Why modeling matters

Power BI's engine (VertiPaq) is a columnar, compressed, in-memory store. It adores tall, narrow tables and simple one-to-many relationships. A well-designed model means: fast reports, tiny files, simple DAX. A bad model means: 200-line DAX hacks, wrong totals, slow refreshes. Modeling is the highest-leverage skill in this course.

📘 3.2 Facts vs dimensions (the two kinds of tables)

Golden rule: filter/slice by dimension columns, aggregate fact columns.

📘 3.3 Star schema vs snowflake

        Customers
            │
Products ── SALES ── Date          ← star: dims surround one fact (GOAL)
                      
Product → SubCategory → Category   ← snowflake: dims normalized further (avoid in Power BI)

Snowflaking (normalizing like a database) feels tidy but adds relationships, ambiguity risk, and slower queries. In Power BI: denormalize dimensions into flat, wide tables and always aim for a star. One star per business process; multiple facts are fine if they share dimensions.

📘 3.4 Relationships (Model view)

Power BI usually auto-detects relationships on load (by matching column names). Expert settings (File → Options → Current File → Data Load): turn OFF "Autodetect new relationships" — detect them once, verify by eye, then own them.

• Many-to-many (*:*) exists (e.g., bridge tables for budgets at different granularity) but treat it as a calculated decision, never an accident.
• Ambiguity: if two paths could filter the same table, Power BI deactivates one. Symptoms = "can't determine relationship" errors or weird blanks.
• Referential integrity: fact rows with keys missing in the dimension land in an automatic (Blank) member in visuals. Clean data + Left Anti joins in Power Query guard against it.

📘 3.5 The Date table — non-negotiable

Time intelligence (YTD, YoY…) requires a proper Date table: one row per day, continuous, no gaps, covering your full date span, with a unique Date column. Never rely on Power BI's hidden Auto date/time tables (they bloat memory — disable in Options → Data Load).

Create it with DAX (Modeling → New table):

Date = 
ADDCOLUMNS (
    CALENDAR ( DATE ( 2023, 1, 1 ), DATE ( 2025, 12, 31 ) ),
    "Year", YEAR ( [Date] ),
    "Month Name", FORMAT ( [Date], "MMMM" ),
    "Month Number", MONTH ( [Date] ),
    "Year Month", FORMAT ( [Date], "YYYY-MM" ),
    "Quarter", "Q" & QUARTER ( [Date] ),
    "Year Quarter", YEAR ( [Date] ) & "-Q" & QUARTER ( [Date] ),
    "Weekday", FORMAT ( [Date], "dddd" ),
    "Weekday Number", WEEKDAY ( [Date], 2 )
)

Then: right-click the table → Mark as date table → choose the Date column → relate Date[Date] (1) → Sales[OrderDate] (*) and Date[Date] (1) → Targets[MonthStart] (*).

📘 3.6 Model hygiene checklist (do these reflexively)

1. Hide from Report View all keys/IDs (CustomerID, ProductID in fact and dims unless needed), sort-helper columns (Month Number, Weekday Number).
2. Sort By Column: Month Name → sort by Month Number; Weekday → Weekday Number. (Fixes alphabetically sorted charts: Apr, Aug, Dec…)
3. Default summarization: keys/IDs → Don't summarize; currency/quantity → Sum.
4. Formats: set currency ₹ #,##0, percentages 0.0%, dates dd-MMM-yyyy — in the model, so every visual inherits them.
5. Data Category (Column tools): City→City, State→State or Province → maps geocode correctly.
6. Hierarchies (right-click column → Create hierarchy): Category → SubCategory → ProductName; State → City.
7. Display folders (Model view, select a measure → Properties) keep 50+ measures tidy: _Time Intelligence, _KPIs…
8. Rename everything for humans: Units Sold, not SumOfQty_final2.

🧪 LAB 3 — Build the ShopKart star schema (60 min)

1. New PBIX. Power Query: load all four CSVs (Sales, Customers, Products, Targets). Verify types (dates→Date, money/prices→Whole Number, Discount→Decimal). Close & Apply.
2. Model view. Verify: Sales[CustomerID] → Customers[CustomerID] and Sales[ProductID] → Products[ProductID] exist as *:1, single direction. If missing: drag key-to-key.
3. Create the Date table (code above), mark as date table, relate to Sales[OrderDate] and Targets[MonthStart].
4. Add a tiny Measures table (the pro trick): Home → Enter Data → empty table named _Measures → in Report view create any measure; it must live in _Measures; then delete the dummy column. The table jumps to the top with a calculator icon. All future measures live here.
5. Apply hygiene: hide all ID columns; Month Name sort-by Month Number; formats (₹ on prices/costs); set City/State data categories; build hierarchies.
6. Sanity visuals: Card = Sum of Quantity; Bar = Category by Sum of Quantity; Table = Region + Customers count; Line = Month Name (sorted!) by Quantity. If "Sort by column" worked, months run Jan→Dec.
7. Save: ShopKart_Model.pbix. This file is your base for Modules 4–8 — keep it safe.

Granularity discussion (journal it): Sales is at order-product-day grain; Targets is at region-month grain. They connect only through shared dimensions (Date, geography via Customers→…→Targets? — careful!). Notice: Targets has Region and MonthStart but no direct link to Customers. This forces correct, intentional filtering — and is why in Project 1 you'll relate Targets only to Date and filter region via its own column.

💪 Exercises

1. Draw (paper or visuals) the full schema with cardinalities and arrows of filter direction.
2. Add UnitCost × Quantity logic question: where should COGS be computed — Power Query column, calculated column, or measure? Defend each option's tradeoff.
3. Add a second Date relationship: Customers[JoinDate] → Date[Date]. Why is it inactive? (Hint: existing path Customers→Sales→Date.)
4. Create hierarchy Product Drill: Category → SubCategory → ProductName; use it in a matrix and drill down/up.
5. Fix this real-world smell: a colleague relates Targets[Region] directly to Customers[Region] (: relationship). Explain the risk (non-additivity, overcounting) and propose the star-correct alternative.
6. Set Products[ProductName] → Sort by… nothing needed; instead create Products by PriceBand exercise from Module 2 and sort PriceBand Low→Mid→High via a hidden order column. (Advanced: do it in Power Query with a conditional + index.)

❓ Quiz

1. Fact vs dimension — one-line definitions. 2. Why avoid snowflaking in Power BI? 3. Default cross-filter direction you trust, and why. 4. Two ways an inactive relationship can be used. 5. Three rules that make a valid Date table. 6. What creates a (Blank) row in a slicer from a dimension? 7. Why turn OFF auto date/time?

✅ Answers

1. Facts = measurable events (rows to aggregate). Dimensions = descriptive entities you filter/group by.
2. More relationships → more ambiguity, slower filters, harder DAX; VertiPaq compresses wide dims well, so normalization buys nothing.
3. Single. Bidirectional filters multiply filter paths → ambiguity and surprises; use DAX (CROSSFILTER) for the rare exception.
4. USERELATIONSHIP inside CALCULATE; or refresh/analysis in Power Query instead. (The relationship stays active=off in the UI.)
5. One row per date, contiguous/no gaps, unique Date column (and covering the model's full date range); then Mark as date table.
6. Fact keys with no matching dimension row (broken referential integrity).
7. Hidden auto date tables are created per date column, inflating file size and confusing time-intelligence behavior.

✅ Mastery checklist

• My ShopKart model is a clean star (screenshot it — portfolio piece #1)
• Date table marked, sorted, related twice; month names sort correctly
• Keys hidden, categories set, currency formatted at the model level
• I can articulate why: tall/narrow, single direction, shared dims

Next: Module_04_DAX_Fundamentals.md — time to write the language.

Module 04 — DAX Fundamentals: Speak the Language of Power BI

DAX (Data Analysis Expressions) is the formula language of Power BI. 20% of DAX covers 80% of business needs — this module is that 20%. Module 05 is the other 80% of the power.

🎯 Objectives

Measures vs calculated columns · row context vs filter context · core functions · CALCULATE (your first taste of the engine room) · time intelligence on a marked Date table · the 10 measures every business asks for.

📘 4.1 Three places DAX lives

A measure is just a named formula: Total Sales = SUM(...), stored in _Measures, evaluated wherever dropped.

📘 4.2 The two evaluation contexts (the whole game)

• Row context = "the current row". Exists in calculated columns and iterating functions (SUMX, FILTER). DAX reads [Quantity] * [UnitPrice] row by row.
• Filter context = the set of filters coming from the visual: row/column fields, slicers, filters pane, other visuals cross-filtering. A measure's result = formula evaluated under the current filter context. The same [Total Sales] shows ₹18.2M on a card (no filters) and ₹84,520 inside a matrix row (filtered to one product, one month, one region).

📌 The mantra: Measures are computed in filter context. Calculated columns are computed in row context. Re-read after every exercise until it feels obvious.

📘 4.3 Core function toolbox

SUM / AVERAGE / MIN / MAX (Table[Column])        -- aggregations over a column
COUNT (col) / COUNTROWS (table) / DISTINCTCOUNT (col)
DIVIDE ( [A], [B] )                              -- safe division (no div/0)
IF ( cond, a, b )   /   SWITCH ( TRUE(), cond1, r1, cond2, r2, default )
COALESCE ( x, 0 )   BLANK()   ISBLANK ( x )
SUMX ( table, expression )   -- row-by-row then sum (iterator family: MINX, MAXX, AVERAGEX...)
&& (and)  || (or)   IN { "a", "b" }

Syntax culture: Table[Column] for columns, [Measure] for measures — never write Table[Measure] (it doesn't exist) or a bare [Column] inside a measure (DAX can't pick which row's value → error: "single value cannot be determined").

📘 4.4 CALCULATE — the most important function in all of Power BI

CALCULATE ( <expression>, <filter1>, <filter2>, … ) evaluates the expression inside a modified filter context. Filters are combined with AND.

West Sales = CALCULATE ( [Total Sales], 'Customers'[Region] = "West" )

% of All Products =
DIVIDE (
    [Total Sales],
    CALCULATE ( [Total Sales], ALL ( 'Products' ) )   -- remove product filters
)

Simple boolean filters (Column = "x") work in CALCULATE; complex ones need FILTER/KEEPFILTERS — Module 05. ALL removes filters; ALLEXCEPT keeps some; REMOVEFILTERS is the readable modern name. Filter on dimension columns, not whole fact tables (speed!).

📘 4.5 Time intelligence (needs your marked Date table)

Sales YTD   = TOTALYTD ( [Total Sales], 'Date'[Date] )           -- also TOTALMTD, TOTALQTD
Sales LY    = CALCULATE ( [Total Sales], SAMEPERIODLASTYEAR ( 'Date'[Date] ) )
Sales PrevMonth = CALCULATE ( [Total Sales], PREVIOUSMONTH ( 'Date'[Date] ) )
Sales 6M Ago    = CALCULATE ( [Total Sales], DATEADD ( 'Date'[Date], -6, MONTH ) )
YoY % = DIVIDE ( [Total Sales] - [Sales LY], [Sales LY] )
MoM % = VAR Cur = [Total Sales]
        VAR Prv = [Sales PrevMonth]
        RETURN DIVIDE ( Cur - Prv, Prv )

These work because, for the month on a visual, time-intel functions reshape the Date filter (e.g., expand July → Jan 1–Jul 31 for YTD).

📘 4.6 Formatting & housekeeping

Modeling tab with a measure selected → set format string (₹ #,##0 or 0.0%), display folder (_Time Intelligence). Dynamic title trick and format-by-measure come in Module 06.

🧪 LAB 4 — The 10 measures every business asks for (75 min)

In ShopKart_Model.pbix, all measures into _Measures, display folder _Core KPIs:

1. Total Sales = SUMX ( Sales, Sales[Quantity] * Sales[UnitPrice] * ( 1 - Sales[Discount] ) ) — note: no Revenue column exists; SUMX computes it row by row. Format ₹.
2. Total Cost = SUMX ( Sales, Sales[Quantity] * RELATED ( Products[UnitCost] ) ) ⚠️ RELATED pulls from the 1-side of the relationship (Products) into the many-side context — first taste of relationship-aware DAX.
3. Profit = [Total Sales] - [Total Cost]
4. Margin % = DIVIDE ( [Profit], [Total Sales] )
5. Units Sold = SUM ( Sales[Quantity] )
6. Orders = COUNTROWS ( Sales ) and Avg Order Value = DIVIDE ( [Total Sales], [Orders] )
7. Customers = DISTINCTCOUNT ( Sales[CustomerID] )
8. Sales YTD, Sales LY, YoY % (formulas above — write them yourself, don't paste).
9. % of Category Sales = DIVIDE ( [Total Sales], CALCULATE ( [Total Sales], ALLEXCEPT ( Products, Products[Category] ) ) ) — test in a matrix on SubCategory.
10. Target = SUM ( Targets[SalesTarget] ) and Achieved % = DIVIDE ( [Total Sales], [Target] ) — validate only in 2025 with Region on rows (granularity!).

Test bench (build this page): Card row: Total Sales, Profit, Margin %, YoY %. Matrix: Year → Month Name rows × Total Sales / YTD / LY / YoY %. Bar: Category by Total Sales with % of Category as tooltip. Slicers: Year, Region, Segment. Click around and watch measures recompute — that recompute is filter context becoming real knowledge.

💪 Exercises (write, verify, then compare)

1. Customers Buying Electronics = count of customers whose visible sales are electronics.
2. Discount Given (₹) = SUMX of quantity × price × discount.
3. High-Discount Orders = count rows with discount ≥ 15%. (COUNTROWS ( FILTER ( … ) ))
4. Best Month Sales = the MAX of monthly sales. Hint: MAXX ( VALUES ( 'Date'[Year Month] ), [Total Sales] ) — save the "why VALUES" question for Module 5.
5. QTD Sales + MTD Sales.
6. West % of India = West sales / all-region sales (ALL ( Customers )).
7. Journal: why is Margin % wrong if written as SUMX ( Sales, ... ) / COUNTROWS(...)? Rewrite any three measures using variables (VAR … RETURN) to match house style; Module 5 explains why pros always do.

✅ Solutions

1. Customers Buying Electronics =
   CALCULATE ( DISTINCTCOUNT ( Sales[CustomerID] ), Products[Category] = "Electronics" )
2. Discount Given = SUMX ( Sales, Sales[Quantity] * Sales[UnitPrice] * Sales[Discount] )
3. High-Discount Orders = COUNTROWS ( FILTER ( Sales, Sales[Discount] >= 0.15 ) )
4. Best Month Sales = MAXX ( VALUES ( 'Date'[Year Month] ), [Total Sales] )
5. QTD = TOTALQTD ( [Total Sales], 'Date'[Date] ) ; MTD = TOTALMTD ( [Total Sales], 'Date'[Date] )
6. West % = DIVIDE ( CALCULATE ( [Total Sales], Customers[Region] = "West" ),
                     CALCULATE ( [Total Sales], ALL ( 'Customers' ) ) )
7. A ratio needs aggregates of *both* sides over the same filter context, not row-level division then aggregation — that's "average of ratios" ≠ ratio of totals.

❓ Quiz

1. Row vs filter context — the mantra, from memory. 2. Why measures over calculated columns (give two reasons)? 3. What does ALL ( 'Products' ) do inside CALCULATE? 4. Why does time intelligence fail without a marked Date table? 5. VALUES vs DISTINCT on a column — what's the sneaky difference? 6. Fix: Discount % = [Discount] * 100 errors in a measure. Why?

✅ Answers

1. Measures → filter context; columns/iterators → row context.
2. No model bloat (computed at query time, per visual) + they respond to slicers — a column can't.
3. Removes every filter coming from the Products table → denominator = all products.
4. TI functions need a contiguous, marked Date table to reshape date filters reliably; otherwise results are silently wrong or blank.
5. Both unique values, but VALUES includes the blank row created by broken referential integrity; DISTINCT doesn't.
6. [Discount] is a bare column in measure-land — no single value. Wrap in an aggregation/iterator: AVERAGE ( Sales[Discount] ).

✅ Mastery checklist

• 10 core measures built, formatted, and in display folders
• I can predict a measure's result when I click a slicer
• I wrote time-intel measures from memory
• I can explain every formula in this module aloud

Next: Module_05_DAX_Advanced.md — context transition, iterators, and the patterns that make you dangerous.

Module 05 — DAX Mastery: CALCULATE, Context Transition & Expert Patterns

Everyone can SUM. What separates experts is mastery of filter context manipulation. This module rewires your brain — expect a week of "aha, so THAT'S why".

🎯 Objectives

Deep CALCULATE · context transition · variables · iterators & virtual tables · the filter-control family (ALL/ALLEXCEPT/ALLSELECTED/KEEPFILTERS…) · 12 production patterns · debugging with DAX Studio · visual calculations.

📘 5.1 CALCULATE, properly understood

CALCULATE ( expr, f1, f2, … ) does two things: ① modifies the filter context (replace roles below), ② evaluates expr in the new context.

Key rules experts recite:

1. Filters are tables: [Region] = "West" is syntax sugar for FILTER ( ALL ( Customers[Region] ), Customers[Region] = "West" ).
2. Multiple filters = AND across them; OR needs || inside one FILTER/KEEPFILTERS or the IN set.
3. By default each filter replaces existing filters on those columns. KEEPFILTERS switches to intersect (adds to slicers instead of overriding them).
4. Filter on dimension columns, never whole fact tables (FILTER ( Sales, … ) materializes the giant table → slow).

-- Replace (default): ignores the user's Region slicer
West forced = CALCULATE ( [Total Sales], Customers[Region] = "West" )
-- Intersect: respects slicer, narrows to West when visible
West respect = CALCULATE ( [Total Sales], KEEPFILTERS ( Customers[Region] = "West" ) )

📘 5.2 Context transition — the dragon you must slay

When CALCULATE (or a measure reference, which hides a CALCULATE) runs inside a row context, the current row's values become filters: that's context transition. Two consequences:

• In a calculated column, [Total Sales] evaluated per row filters the model to that row's customer/product/date → not "grand total". Wrap in ALL if needed.
• Inside iterators like SUMX, measures transition — columns don't. SUMX ( Products, [Total Sales] ) ≠ SUMX ( Products, Products[UnitPrice] ): the first evaluates per-product filtered sales (correct), the second ignores context inside the sum.

📌 Mastery test: if you can explain why SUM ( Sales[Quantity] ) (no CALCULATE, no transition) behaves differently from SUMX ( Sales, [Units Sold] ) inside a calculated column on Date — you've slain the dragon.

📘 5.3 Variables — house style of every pro

YoY % = 
VAR CurrentSales = [Total Sales]
VAR PriorSales   = CALCULATE ( [Total Sales], SAMEPERIODLASTYEAR ( 'Date'[Date] ) )
RETURN
    IF ( ISBLANK ( PriorSales ), BLANK (), DIVIDE ( CurrentSales - PriorSales, PriorSales ) )

• VAR = computed once, in the context where defined (constant afterward) → clarity + speed.
• Formatting discipline: one variable per line, RETURN on its own line, blank line before complex sections. Comment with -- or //.

📘 5.4 Iterators & virtual tables

Iterators (SUMX, AVERAGEX, MAXX, MINX, RANKX, COUNTX, CONCATENATEX) = X ( <table>, <expression> ) — loop rows (row context), evaluate, aggregate. Pass small virtual tables from functions:

-- Inactive JoinDate relationship, awakened:
Customers Joined = CALCULATE ( COUNTROWS ( Customers ), USERELATIONSHIP ( Customers[JoinDate], 'Date'[Date] ) )
-- Per-measure bidirectionality:
Sales by Product-to-Region = CALCULATE ( [Total Sales], CROSSFILTER ( Sales[ProductID], Products[ProductID], BOTH ) )

📘 5.5 The 12 production patterns (type each, don't paste)

1) Running Total = CALCULATE ( [Total Sales], FILTER ( ALL ( 'Date' ), 'Date'[Date] <= MAX ( 'Date'[Date] ) ) )
   -- smarter variant: DATESBETWEEN('Date'[Date], BLANK(), MAX('Date'[Date]))
2) 3-Month Moving Avg = AVERAGEX ( DATESINPERIOD ( 'Date'[Date], MAX ( 'Date'[Date] ), -3, MONTH ), [Total Sales] )
3) % of Visible Total = DIVIDE ( [Total Sales], CALCULATE ( [Total Sales], ALLSELECTED ( 'Products'[ProductName] ) ) )
4) Product Rank = IF ( ISINSCOPE ( 'Products'[ProductName] ),
                      RANKX ( ALLSELECTED ( 'Products'[ProductName] ), [Total Sales] ) )
5) New Customers = VAR Cur = VALUES ( Sales[CustomerID] )
                   VAR Prev = CALCULATETABLE ( VALUES ( Sales[CustomerID] ),
                              FILTER ( ALL ( 'Date' ), 'Date'[Date] < MIN ( 'Date'[Date] ) ) )
                   RETURN COUNTROWS ( EXCEPT ( Cur, Prev ) )
6) Semi-additive (snapshot/inventory style): Closing = CALCULATE ( [Total Sales], LASTDATE ( 'Date'[Date] ) )
7) Top-N Others (dynamic): combine pattern 4 with TOPN and an IF rank <= N branch
8) Actual vs Budget at different grain via TREATAS (skip shared dimension):
   Budget = CALCULATE ( SUM ( Targets[SalesTarget] ),
              TREATAS ( VALUES ( Customers[Region] ), Targets[Region] ) )
9) Safe YoY for incomplete Year: hide YoY % when prior-year data partial:
   = IF ( NOT ISBLANK ( [Sales LY] ) && [Sales LY] <> 0, [YoY %] )
10) Dynamic title = "Sales: " & COALESCE ( SELECTEDVALUE ( Customers[Region] ), "All Regions" )
                & " | " & COALESCE ( SELECTEDVALUE ( 'Date'[Year] ), "All Years" )
11) Pareto % (cumulative share) — matrix on ProductName desc by sales:
    Pareto % = VAR Cur = [Total Sales]
               RETURN DIVIDE (
                   SUMX ( FILTER ( ALLSELECTED ( 'Products'[ProductName] ), [Total Sales] >= Cur ), [Total Sales] ),
                   CALCULATE ( [Total Sales], ALLSELECTED ( 'Products'[ProductName] ) ) )
12) Customers joined per month (USERELATIONSHIP above) or basket analysis (filtered copy of Products via TREATAS + CALCULATETABLE)

📘 5.6 Debugging like an engineer

1. Build on a table visual, one VAR at a time — return intermediate variables to inspect them (RETURN PriorSales).
2. On a matrix, wrong subtotals are almost always context issues: think "what is the filter context right here?" ISINSCOPE/HASONEVALUE shape the answer per level.
3. DAX Studio (free, external tool): View Metrics shows cardinality & size; write queries (EVALUATE SUMMARIZECOLUMNS(...)) to inspect tables; Server Timings shows storage vs formula engine (Module 08 deep-dive).
4. DAX query view in Desktop can now run those same queries natively — use it to test table functions.
5. Error decoder: "single value…" = bare column in measure · circular dependency = two columns each depending on the other via CALCULATE/context transition · wrong totals = filter context logic, or % of parent math.

📘 5.7 Visual calculations (modern, visual-local DAX)

New capability: DAX attached to one visual, with its own tiny grid context — great for running totals & deltas without touching the model: Running sum = RUNNINGSUM ( [Total Sales] ) · MoveAvg = MOVINGAVERAGE ( [Total Sales], 3 ) · Delta = [Total Sales] - PREVIOUS ( [Total Sales] ) · helpers: FIRST, LAST, NEXT, LOOKUP, RANGE, COLLAPSE, EXPAND. Use for presentation-level math; keep business logic in model measures.

🧪 LAB 5 — Upgrade ShopKart (90 min)

1. Rebuild the test-bench page from Module 4; add a Date-level matrix (Year→Month on rows).
2. Implement patterns 1, 2, 3, 4, 5, 9, 10 from §5.5 into _Measures (folder _Advanced).
3. Dynamic title measure on a card; confirm it changes with slicers.
4. Ranking visual: bar with Product Rank ≤ 10 via a visual-level filter — then explain why ALL vs ALLSELECTED changes ranks when you toggle the Region slicer.
5. Write Customers Joined (USERELATIONSHIP) and chart it by month.
6. Bonus gauntlet: Pareto chart — column chart of sales by product (desc) + Pareto % line. When cumulative crosses 80%, roughly how many of 26 products drive the business?

💪 Exercises (graded sets — solutions after)

A. Warm-up: A1) West 2025 Sales = ? (two filters). A2) Rewrite high-discount orders with COUNTROWS(CALCULATETABLE(...)). A3) % of Region such that rows = City, denominator = the region total (ALLEXCEPT twice? or ALL(Cities)… solve it). B. Context drills: B1) Predict, then verify: does CALCULATE ( [Total Sales], REMOVEFILTERS ( 'Date' ) ) change inside a Year-on-rows matrix? Why? B2) Write Avg Customers per Month = AVERAGEX over visible Year-Months of distinct customers. B3) Fix a report where YoY% shows -100% for future months. C. Boss level: C1) Rolling 12-month sales ending at current month. C2) Returning customers (bought in prior 90 days, distinct count). C3) Sales of "products never sold in West" via FILTER+NOT IN CALCULATETABLE.

✅ Solutions (selected)

A1 = CALCULATE ( [Total Sales], Customers[Region] = "West", 'Date'[Year] = 2025 )
A2 = COUNTROWS ( CALCULATETABLE ( Sales, Sales[Discount] >= 0.15 ) )
A3 % of Region = DIVIDE ( [Total Sales],
                          CALCULATE ( [Total Sales], ALL ( 'Customers'[City] ), VALUES ( 'Customers'[Region] ) ) )
B1 — No change per row: REMOVEFILTERS('Date') clears the row's Year filter → same grand value each row.
B2 = AVERAGEX ( VALUES ( 'Date'[Year Month] ), CALCULATE ( DISTINCTCOUNT ( Sales[CustomerID] ) ) )
B3 Guard: = VAR LY = [Sales LY] RETURN IF ( ISBLANK ( LY ), BLANK(), DIVIDE ( [Total Sales] - LY, LY ) )
C1 = CALCULATE ( [Total Sales], DATESINPERIOD ( 'Date'[Date], MAX ( 'Date'[Date] ), -12, MONTH ) )
C2 = VAR Prev90 = CALCULATETABLE ( VALUES ( Sales[CustomerID] ),
                     DATESINPERIOD ( 'Date'[Date], MIN ( 'Date'[Date] ) - 1, -90, DAY ) )
     RETURN COUNTROWS ( INTERSECT ( VALUES ( Sales[CustomerID] ), Prev90 ) )
C3 = CALCULATE ( [Total Sales],
         FILTER ( VALUES ( Products[ProductName] ),
                  NOT Products[ProductName] IN CALCULATETABLE ( VALUES ( Products[ProductName] ), Customers[Region] = "West" ) ) )

❓ Quiz

1. RESTORE vs INTERSECT — which CALCULATE behavior is default and which needs KEEPFILTERS? 2. What exactly happens in context transition (one precise paragraph)? 3. When is VALUES risky? 4. Why is FILTER(ALL(Sales), ...) a performance sin — and what's the fix? 5. ALLSELECTED vs ALL in ranking: which ignores slicers? 6. Why does a measure referenced inside SUMX "see" each row, but a column reference doesn't?

✅ Answers

1. Replace is default; KEEPFILTERS = intersect with the existing context.
2. Row context → CALCULATE converts the column values of the current row into equivalent filters on the model, then evaluates the expression in that new filter context (this is also why measure references trigger it: every measure is implicitly wrapped in CALCULATE).
3. It includes the blank member from broken referential integrity → phantom +1 counts / ghost rows.
4. It scans/materializes the entire fact table per evaluation. Fix: filter on dimension columns and let relationships reach the fact (e.g., CALCULATE (..., Products[PriceBand]="High") or KEEPFILTERS on a keys column).
5. ALL ignores slicers AND visual filters; ALLSELECTED keeps slicer/page selections — ranking adapts to the user's view.
6. Measures carry hidden CALCULATE → context transition per iterated row; raw columns sit in pure row context and never become filters by themselves.

✅ Mastery checklist

• I can write patterns 1–12 from memory-ish, recognizing the shape needed
• I explain context transition without notes (test yourself on a friend/me)
• All measures refactored to VAR/RETURN with comments
• I'm comfortable in DAX Studio / DAX query view for debugging

Next: Module_06_Visualization_Design.md — make it beautiful, interactive, undeniable.

Module 06 — Visualization & Report Design: Dashboards People Actually Use

A perfect model with an ugly report gets ignored. A great report tells a story in 5 seconds, invites exploration, and never lies.

🎯 Objectives

Choose the right visual every time · formatting & conditional color · slicers/filters/drill/trillthrough · bookmarks & buttons (real interactivity) · themes, tooltips pages, field parameters · design principles & accessibility · mobile layout.

📘 6.1 The chart chooser (tattoo this)

Newer friends: small multiples (one chart per category automatically), sparklines in table/matrix, new card visual (multi-card with layouts). Avoid the temptation of "cool" — boring and clear beats exciting and confusing.

📘 6.2 Formatting & conditional logic

• Format pane: data colors, axis (log scales off by default — good), data labels (position, density), gridlines (usually off), titles = takeaways ("South grew 31% YoY", not "Sales by Region").
• Conditional formatting (fx buttons on background/font/icons/web URL): by Field value = a measure that returns colors/hex → full dynamic control:

  Margin Color = IF ( [Margin %] >= 0.35, "#2E7D32", IF ( [Margin %] >= 0.25, "#F9A825", "#C62828" ) )

• Dynamic format strings (measure → Format dropdown → Dynamic): one measure showing ₹ Cr vs ₹ Lakh by size, or "+12.4% ▲" logic.
• Themes: View → Themes (built-in gallery) or a custom JSON theme — brand colors, fonts, defaults applied to every visual. Build once per company. (Format everywhere: textboxes/shapes for headers, consistent 8-pt grid.)

📘 6.3 Interactivity machinery

⭐ Bookmarks + buttons = "apps", not reports

Bookmark = saved view state (filters, slicers, visibility + which visuals affected, current page). Window: View → Bookmarks pane + Selection pane (rename every object: btn_FilterPanel_Open, img_Logo — undisciplined naming makes bookmarks hell).

Pattern 1 — Collapsible filter panel: build a shape-backed panel of slicers off-canvas-style → Bookmark A "Filters Open" (panel visible + down-arrow icon) → Bookmark B "Filters Closed" → two buttons/images each triggering the opposite bookmark (assign in Action). Update bookmarks to affect Selected visuals only (select the slicer group first) so they don't trap user filters — uncheck Data in the bookmark options when you only toggle visibility.

Pattern 2 — Chart-type toggle / KPI switcher: two overlapping charts; bookmarks swap visibility; a single button toggles.

Pattern 3 — Reset filters: bookmark the page's default state → "Reset" button.

Buttons: Page navigation, Back, Bookmark, Drillthrough, Q&A, Web URL (Web URL buttons can even open mail links). Personal bookmarks (Service) = each user's saved views.

📘 6.4 Field parameters & measure switchers

Modeling → New parameter → Fields → pick measures or columns + toggle Add slicer to page. Power BI creates a table whose selection drives a visual's value/axis → user-controlled "Show by: Sales | Profit | Margin %". Explains itself in the DAX (NAMEOF references). For time-intel-switch patterns, professionals historically used calculation groups (Tabular Editor) — still the industrial tool for "Current / YTD / YoY%" selectors (Module 08).

📘 6.5 Design principles (the designer's dozen)

1. 5-second rule: page must answer its core question at a glance. Title = the answer, not the topic.
2. F-layout: KPI row across the top, main trend center-left, breakdowns right, details below.
3. One page, one story. 3–7 visuals max; detail demands its own page + drillthrough.
4. Align to a grid; consistent sizes; white space is a feature.
5. Color with intent: brand palette (60/30/10 rule), one accent for attention, red/green only for bad/good, never both meanings.
6. Typography hierarchy: big KPI numbers (28–40+), medium titles (12–14), small labels (9–10). One font family (Segoe UI / DIN).
7. Label data directly where possible; ditch legends when direct labels exist; round aggressively (₹1.8M not ₹1,847,293.42).
8. Kill chartjunk: heavy gridlines, borders, 3-D, shadows.
9. Avoid dual axis unless audiences expect it; if used, color-match axis to series.
10. Accessibility: colorblind-safe palettes (test: deuteranopia), contrast ≥ 4.5:1, Alt text on every visual (Format → General → Alt text), set Tab order (Selection pane). 8% of men are colorblind — red/green-only coding is malpractice.
11. Numbers need context: always pair a KPI with vs-target, YoY or sparkline.
12. Mobile first for execs: check View → Mobile layout, arrange a vertical one-hand scroll page.

🧪 LAB 6 — "ShopKart Executive Overview" (2 hrs)

Canvas 16:9.

1. Header bar (rectangle + logo shape + dynamic title measure from Module 5 + report date).
2. KPI row: 4 cards — Total Sales, Profit, YoY %, Achieved % of Target — each with Margin Color-style conditional font color and small subtitle (₹ vs Target).
3. Trend: line+column combo — columns = Total Sales by Month, line = Sales LY, data labels off (tooltip detail instead).
4. Breakdowns: bar = Sales by Category (with drill down to sub-category); map = bubbles by State/City sized by Sales; decomposition tree pinned bottom-right: Sales → split by Region → Segment → Category.
5. Slicer panel on a collapsible bookmark: Year, Region, Segment (+ Search on). Add sync across pages OFF for now; Reset button (Pattern 3).
6. Page 2 — Customer drillthrough: matrix of customers, Units/Sales/Margin; set drillthrough field CustomerName; auto back button; conditional bar on Margin %.
7. Page 3 — tooltip page "TT Product": product name, 12-month sparkline-like line, top-3 customers; wire to the category bar visual.
8. Apply a theme (choose one + set title colors), align to grid, set alt texts, publish-ready.
9. Critique pass: squint test (is the hierarchy obvious?), 5-second test on a colleague, colorblind sim, mobile layout arranged.

Deliverable: screenshots of all three pages → portfolio asset #2.

💪 Exercises

1. Rebuild a famous chart: pick any Economist/FT graphic; recreate it in Power BI with the dataset. (Best design training there is.)
2. Implement the chart-type toggle (Pattern 2) between map and filled-map-style table.
3. Make Achieved % flip between vs Target and vs LY using a field parameter.
4. Conditional-format the matrix so any month with Margin % < 25% glows amber — measure-driven, not gradient.
5. Full edit interactions audit of Lab 6: decide, per visual pair, filter vs highlight vs none. Document two choices in your journal.
6. Bad dashboard clinic: I describe one — 3 pages, 14 visuals/page, rainbow pie charts, unlabeled axes. List 8 concrete fixes (do it before looking at §6.5).

❓ Quiz

1. Pie or bar for 8 categories — and why? 2. What three things can a bookmark capture — and which should you uncheck for a pure visibility toggle? 3. Drill down vs drillthrough vs tooltip page. 4. Two ways to give users a "switch the measure" experience. 5. Name four accessibility musts. 6. What is the 5-second rule?

✅ Answers

1. Bar. Humans compare lengths far more accurately than angles/areas; pies hide differences (esp. >5 slices).
2. Data (filters/slicer state), Display (visibility/spotlight), Current page. Uncheck Data for visibility-only toggles so user filters survive.
3. Drill down = descend a hierarchy within one visual; drillthrough = jump to another page filtered by the clicked point; tooltip page = hover overlay mini-report.
4. Field parameters (no code, works in axis too) and calculation groups via Tabular Editor (industrial, cleaner for time-intel).
5. Colorblind-safe palette, 4.5:1 contrast, alt text on visuals, logical tab order (+ not encoding by color alone).
6. A new viewer must grasp the page's main message within 5 seconds.

✅ Mastery checklist

• Executive Overview built, themed, critiqued, screenshot in portfolio
• Bookmarks/buttons panel works without trapping user filters
• Drillthrough + tooltip pages wired
• I can reuse this design language on any dataset in under an hour

Next: Module_07_Service_Sharing_Security.md — publish it, protect it, refresh it.

Module 07 — Power BI Service: Publish, Share, Refresh & Secure

A report on your laptop helps one person. Published and governed right, it runs a company. This module is the "other half" of Power BI that interviewers always probe.

🎯 Objectives

Publish & workspace lifecycle · apps & audiences · scheduled refresh & gateways · sharing models & permissions · Row-Level Security (static + dynamic) · endorsements, lineage, usage metrics, teamwork in the Service.

📘 7.1 Mental model

Publish (Desktop → Publish) uploads the .pbix as two sibling artifacts in a workspace: the semantic model (data + relationships + measures + security) and the report (pages/visuals). Many reports can sit on one semantic model — one version of the truth. Dashboards (Service-only boards of pinned tiles from many reports) still exist; apps are the modern distribution vehicle.

Licenses (2026): Free = build & personal My workspace only · Pro ($14/user/mo; in M365 E5) = collaborate/share in workspaces · Premium Per User ($24) = advanced features (large models, deployment pipelines, AI) per user · Fabric capacity (F-SKUs) = org-level; viewers with Free licenses can consume content hosted on F64+. Old Premium P SKUs retired for new purchases (Jul 2024) → Fabric F SKUs.

📘 7.2 Workspaces & lifecycle

• Workspace = container/team area for a project/domain (Sales Analytics - Dev). Roles:

• Apps: the polished package of selected content with audiences (groups of users see different tab sets). Update app = push changes without resharing links. Audiences + RLS = "right people, right pages, right rows".
• Pro basics: Lineage view (model ← reports ← dashboards, incl. gateway/dataflows upstream) and impact analysis before changing a shared model.

📘 7.3 Refresh & gateways

Scheduled refresh (semantic model settings): up to 8/day on Pro (48 on Premium/PPU). Needs working credentials for each source: semantic model → Settings → Data source credentials → OAuth2/Windows/Basic + privacy level (Private/Org/Public — privacy levels stop data bleeding between sources; mismatches = the dreaded Formula.Firewall error).

Also know: cloud connections (shareable, managed in Service), mashup errors 101 (wrong privacy level, stale password), and incremental refresh (Module 08). Subscriptions email snapshots of report pages; alerts fire on dashboard KPI tiles crossing thresholds (dashboard tiles only).

📘 7.4 Sharing — choose the right door

Build permission: lets others create new reports on your model (or analyze in Excel). This is the backbone of governed self-service: certified central models + makers building on top.

📘 7.5 Row-Level Security (RLS) — the exam favorite & the enterprise must

Static RLS (small scale): Modeling → Manage roles → West Role: Customers[Region] = "West" → filter flows: Region→Customers→Sales. Test in Desktop: View as role. Assign people: Service → semantic model → Security → add users/groups to the role ( Viewers only; Admins see all).

Dynamic RLS (the real world — one role, any user): security table mapping users to what they may see; DAX on the security table filters by the signed-in user:

1. Enter Data → RegionAccess table: Email | Region (e.g., amit@shopkart.in | North). NO relationship is even needed (cleanest): role DAX on RegionAccess:

   [Email] = USERPRINCIPALNAME()

and relate RegionAccess[Region] → Customers[Region] (*:* is honest here; or filter via TREATAS variant) with security filters flowing RegionAccess→Customers (trim the *:* by giving Customers→RegionAccess direction both? — standard pattern: relationship RegionAccess(Region) *→* Customers(Region) with "apply security filter in both directions" OFF and single direction into Customers; the:` on a 4-value region column is fine).

1. USERPRINCIPALNAME() = the login email (in Desktop, "View as → Other user" to simulate). USERNAME() = DOMAIN\user on-prem.
2. Test matrix: row per manager → only their region's sales; card totals change accordingly. RLS + App audiences = enterprise gold.

OLS (Object-Level Security) hides whole tables/measures (via Tabular Editor/XMLA) — RLS hides rows; OLS hides schema. Static vs dynamic + test-as-role are PL-300 certainties.

📘 7.6 Trust, scale & teamwork features

Endorsements: Promoted (team-validated) / Certified (org-blessed — needs tenant permissions). Sensitivity labels (Microsoft Purview) travel with exports to Excel/PPT. Usage metrics report per workspace: who views what — use it to kill zombie reports. Deployment pipelines (Module 08) manage dev→test→prod. Notifications: data-driven alerts, subscriptions, refresh-failure emails (check yours weekly as admin).

🧪 LAB 7 — Ship ShopKart to the world (90 min)

1. File → Options check nothing pending; Publish → create workspace ShopKart Analytics - Dev → select it.
2. In the Service: explore workspace; open report; pin 2 KPIs to a new dashboard Exec Pulse.
3. Set an alert on the Total Sales card tile (> ₹500,000) — watch the bell.
4. Refresh: Settings → Semantic model → credentials for the CSV sources (if local files: use gateway personal mode, or move files to OneDrive and repoint the source — recommended: repoint to OneDrive → auto sync). Schedule 2×/day refresh.
5. Static RLS: create Region West role; View as role; publish; in Service assign a colleague/test identity (or write the expected effect in journal if solo).
6. Dynamic RLS: build RegionAccess (Enter Data): two fake managers covering all 4 regions, plus your email on North. Role DynamicManagers with [Email] = USERPRINCIPALNAME(). Test as [DynamicManagers, Other user = your own email] → only North shows everywhere. Publish + verify in Service's Test as role.
7. App: Update app → include Report + Dashboard → audiences: "Leadership" (every page) & "Region Managers" (hide the margins page? create it first!) → set permissions → get the app link. Screenshot the app — portfolio asset #3.
8. Set Promoted endorsement on the semantic model; open lineage view; run usage metrics; subscribe yourself to a daily 8 AM email of page 1.

💪 Exercises

1. Explain to your journal why apps beat links: lifecycle, permissions, discoverability.
2. Design workspace strategy for a 3-region company: one workspace per region vs one with audiences+RLS — argue both, pick one.
3. Break refresh on purpose (bad path), read the full error email, fix it — note the 3 most common failure causes.
4. RLS puzzle: a manager owns two regions + should see own team only. Adjust RegionAccess/role DAX (multi-row per email — does anything need to change? why not?).
5. Research task: find in the tenant settings where "Publish to web" is disabled and why; and where Build permission is granted at model level.
6. Alerts vs subscriptions: one-line difference + one use case each.

❓ Quiz

1. Artifacts created on publish and why the split matters. 2. Standard vs personal gateway. 3. The DAX function powering dynamic RLS and how to test without a second human. 4. Who can be assigned to an RLS role in Service? 5. Certified vs Promoted. 6. What's a Formula.Firewall error about? 7. Why "one semantic model, many reports"?

✅ Answers

1. Semantic model + report; the split enables reuse (many reports/Excel on one governed model), separate security & refresh.
2. Standard = shared enterprise gateway, centrally managed, multiple sources/users; Personal = your machine, your account, quick and dirty (machine must be on at refresh time).
3. USERPRINCIPALNAME(); test with View as roles → Other user in Desktop and Test as role in Service.
4. Users/groups with Viewer access to the workspace (members/admins bypass RLS).
5. Promoted = endorsed by the owning team; Certified = governance-approved org-wide (controlled via tenant settings).
6. Privacy-level/data-source-combination blocking in the mashup engine — fix levels per source (typically Organizational for internal sources).
7. Single source of truth: measures fixed once, RLS uniform, one refresh, consistent numbers org-wide.

✅ Mastery checklist

• ShopKart live as an app with RLS + scheduled refresh (or OneDrive sync)
• I can diagram gateway architecture from memory
• Dynamic RLS built + tested with View as
• Portfolio asset #3 captured

Next: Module_08_Performance_Enterprise.md — make it fast, big, and professional.

Module 08 — Performance, Big Models & the Enterprise Toolkit

Anyone can build a dashboard. Experts build one that's still fast at 500 million rows, version-controlled, deployable, and governed. Welcome to the senior-league module.

🎯 Objectives

Storage modes · why models bloat · Performance Analyzer + DAX Studio diagnostics · DAX & model optimization playbook · incremental refresh & aggregations · pro tooling (.pbip/TMDL, Tabular Editor, pipelines, Git) · Fabric & Copilot orientation.

📘 8.1 Storage modes (pick deliberately)

📘 8.2 Why models get slow & fat (and the cures)

VertiPaq charge: cost ≈ column count × cardinality (unique values). Diets:

1. Drop every unused column (facts keep only keys + measures). VBA-ish tag-team: Measure Killer / Bravo (SQLBI); measure what each column costs with DAX Studio → View Metrics.
2. Lower cardinality: split DateTime → Date + Time (or round), drop unique IDs, prefer integers over text keys (but pre-summarized ints beat long GUIDs).
3. Disable Auto date/time (hidden tables per date column!) — you have a real Date table already.
4. Star schema (again) — big flat tables compress worse than star + relationships.
5. Numbers: avoid high-precision decimals, round money; avoid calculated columns that a measure can replace; order-by encoding friendly columns helps compression (let PQ sort big fact by keys).
6. Image/binary/long text out of the model entirely.

📘 8.3 Diagnose, then optimize (never guess)

1. Performance Analyzer (Optimize tab → Start recording → Refresh visuals): per-visual DAX query / Visual display / Other ms + Copy query.
2. Paste into DAX Studio → Server Timings + Query Plan: storage engine (SE) vs formula engine (FE). Fast DAX = most work pushed to SE (simple filters on columns), little FE row-iteration.
3. DAX optimization playbook (memorize):
• Filter dimension columns, not FILTER ( FactTable, … ).
• KEEPFILTERS when filtering by a measure condition over sets: FILTER ( KEEPFILTERS ( VALUES ('Date'[Month] ) ), [Total Sales] > 100000 ).
• Prefer DIVIDE, variables (compute once), and measure references over repeated expressions.
• Avoid nested iterators on big virtual tables; SUMMARIZECOLUMNS for query-style tables; watch out RANKX over 100k-row tables.
• Boolean or heavy FILTER → move logic to the model (flags as columns in dims) or PQ.
• Time-intel sugar funcs are fine; the sin is scanning ALL ( 'Sales' ) to rebuild calendars per-visual.
• Replace whole-table VALUES ( Table ) with VALUES ( Table[Column] ) whenever possible.
4. Report-level speed: fewer visuals/page (each visual = query), fewer slicers (each slicer = query on load), avoid bi-directional relationships & high-cardinality slicer lists, turn off "cross-highlight" where unneeded, sort hidden helper columns out of visuals, images via URLs not embedded.

📘 8.4 Scale features

• Incremental refresh: parameters RangeStart/RangeEnd (DateTime) → filter the fact in PQ (>= RangeStart and < RangeEnd — ensure query folding!) → right-click table → Incremental refresh: store 5 years, refresh last 7 days (+ only refresh complete periods, optional "detect data changes" on a modified-date column). Requires Pro? — Pro yes (Premium needed for large/hybrid). Refresh time collapses from hours to minutes.
• User-defined aggregations: small Import agg table (Sales by Month×Category×Region) + Manage aggregations (GroupBy/Sum/Count mappings + Precedence). Queries hitting aggregated level answer from memory; detail queries drop to DQ fact. Composite magic.
• Automatic aggregations: DirectQuery + Import-driven auto-built aggs (Premium) — turn on, watch hit-rate in the refresh history.
• Hybrid tables: historical Import partitions + today's DirectQuery partition = real-time tail without DQ pain (Premium).
• Query folding discipline (the DQ/incremental enabler): keep steps foldable (View → Native query must stay clickable; avoid index columns, merges on computed columns, Text.* gymnastics after folding). Push cleanup upstream (SQL view) when it breaks folding.

📘 8.5 Professional toolchain

Pattern to rehearse: .pbip in repo → develop on feature branch → PR review the TMDL diff → merge → pipeline deploys Test → gate check (BPA) → Prod workspace → app update.

📘 8.6 Fabric & Copilot (2026 reality-check)

Microsoft Fabric = OneLake + lakehouses/warehouses (Delta), Data Factory, Synapse, Real-Time, and Power BI on top. Analyst impact: data often lands as lakehouse tables → connect Direct Lake → near-instant semantic models, no import refresh, "default semantic model" per lakehouse (always rebuild your own pro model on top). Fabric trial = free capacity to learn this.

Copilot in Power BI (Fabric capacity required): generate report pages from prompts, write/explain DAX, summarize semantic models into narrative visuals. Treat as a fast junior: prompt well ("Create a bar of Profit by Region for 2025 with conditional color on negative values"), verify everything, know that weak models produce weak Copilot output — your modeling skills make AI useful.

🧪 LAB 8 — Optimize ShopKart & taste Fabric (60–75 min)

1. Baseline: Performance Analyzer on your Executive Overview page; screenshot timings.
2. Apply the diet: hide/delete unused columns (Discount in a calc-measure? keep), disable auto date/time, ensure no text keys in visuals, replace any FILTER ( Sales, ...): rewrite with dimension filters. Re-record; compare.
3. Rewrite one expensive measure using variables + KEEPFILTERS; note the SE/FE split change in DAX Studio.
4. Convert ShopKart_Model.pbix → Save as .pbip; open the folder; commit to a local git init repo; edit a measure name in TMDL view; watch Desktop hot-update; git diff the change. (This demo alone wows interviewers.)
5. Read-through (no Premium needed): incremental refresh setup on Sales with RangeStart/RangeEnd as far as Power Query allows; write the exact click-path you'd use in a Premium workspace.
6. Fabric sampler (trial): create workspace on trial capacity → new Lakehouse → upload Sales.csv as table → build a Direct-Lake semantic model → one visual. Note the speed/no-refresh difference in your journal.
7. Optional: Tabular Editor 2 connect → Best Practice Analyzer → fix top 3 warnings (unformatted measures, visible keys, missing descriptions — add descriptions!).

💪 Exercises

1. Order these by cost: drop 1M-row ID column / drop text address column with 50 uniques / hide a column. Explain.
2. A DirectQuery report renders in 12 s/page: list five attack vectors (source indexes, fewer visuals, aggregations…).
3. Write the incremental-refresh PQ filter line and explain why < vs <= matters at boundary.
4. Composite scenario design: 3-year 800M-row fact (DQ) + aggs by Month/Region — describe tables, storage modes, and which visuals hit which.
5. Calculation-group concept: sketch a "Time Intelligence" calc group with items Current/YTD/PY/YoY% that wraps SELECTEDMEASURE(). (Research Tabular Editor docs; write the YTD item expression.)
6. Journal: when would you not use Import mode even in Fabric world?

❓ Quiz

1. VertiPaq's pricing unit is basically…? 2. SE vs FE in one line each. 3. Three reasons auto date/time is evil at scale. 4. Why must incremental-refresh steps fold? 5. User-defined vs automatic aggregations. 6. What does .pbip enable that .pbix can't? 7. Dual mode purpose. 8. Copilot's biggest dependency for quality output?

✅ Answers

1. Column cardinality × rows (storage engine compresses best low-cardinality, value-encoded columns).
2. Storage engine = blinding-fast compressed scans/filters over columns; formula engine = slow row-by-row logic when DAX is too exotic for SE.
3. Hidden table per date column; bloats size; uncontrolled 1900–2100 spans; competes with your real Date table logic.
4. The date-range filter must translate to SQL so only new partitions pull data — non-folding steps force full-table processing.
5. User-defined = you build/map the agg table with precedence; automatic = Premium builds/maintains aggs from query logs — both fallback to DQ on miss.
6. Text-based source control, diffs/PRs, TMDL editing, automation/CI.
7. Looks/behaves Import-fast when cached, falls back to DirectQuery when the cache can't serve the query — keeps composite models responsive.
8. The semantic model's quality (names, relationships, measures) — great models make Copilot look brilliant; mess makes it confidently wrong.

✅ Mastery checklist

• I can profile any report and propose 3 concrete speed-ups with evidence
• Model diet + DAX rewrite both delivered measurable wins
• I've Git-committed a .pbip and edited TMDL
• I can whiteboard Import/DQ/composite/incremental/aggs and Fabric placement for any scenario

Next: Module_09_Capstone_Projects.md — prove it all with portfolio projects.

Module 09 — Capstone Projects: Build Your Expert Portfolio

Certificates open doors; projects close deals. Three builds of increasing independence. Screenshot everything — you'll assemble a public portfolio at the end.

Portfolio rules: ① every project gets a problem statement, a model diagram, and a "decisions" log; ② commit as .pbip to GitHub; ③ capture 4–6 annotated screenshots; ④ write a 150-word summary (business problem → approach → impact). Publish to novypro.com and pin on LinkedIn.

🏆 PROJECT 1 — ShopKart Retail Sales 360 (Guided)

Story: You're ShopKart's first data analyst. The founder wants one app answering: How are we doing, where, on what, and vs plan? Data: your datasets/ (Sales, Customers, Products, Targets). Deliverable: published app with RLS.

Requirements checklist

Milestones (2–3 days, ~6–8 hrs)

1. Model (reuse ShopKart_Model.pbix) + Targets connected to Date; validate target numbers only appear correctly at Region×Month granularity. ⚠️ Decision log: why NOT relate Targets→Customers directly.
2. Measures: build R2–R4; refactor to VAR/RETURN; display folders _KPIs, _Time Intel, _Advanced.
3. Pages: Executive Overview, Products & Pareto, Customers (drillthrough), TT_Product (tooltip). Apply theme, alt text, tab order, mobile layout.
4. Publish → app with two audiences (Leadership sees all; Managers see Overview+Customers) → dynamic RLS RegionAccess → test as role → screenshots.
5. Performance: PA record → apply one model-diet & one DAX fix → record again → write the delta in the log.

Self-grade rubric (100): correctness 30, model quality 20, design/story 20, interactivity 15, performance/ops 15. ≥85 = portfolio-ready. Stretch: field-parameter KPI switcher; new-card visual KPIs; a decomposition tree answering "why did West dip in October?"; a /visual-calculation running sum replacing pattern 1 — compare both.

🥈 PROJECT 2 — Retail Discovery HR Analytics (Semi-Guided)

Story: HR Director of a 4,000-person retailer: "Attrition is eating us. Show me where, who, and why — and let my regional HR leads see only their region." Data: fetch IBM HR Analytics Attrition (Kaggle) or similar HR dataset (~1,500 rows: Age, Department, JobRole, MonthlyIncome, YearsAtCompany, OverTime, Satisfaction, Attrition…). Add a second truth: build a small HeadcountTargets.csv yourself in Enter Data style.

You must figure out (hints, not steps)

• Grain analysis: one row per employee → headcount = COUNTROWS, attrition = CALCULATE ( [Employees], HR[Attrition] = "Yes" ), Attrition Rate = DIVIDE ( leavers, avg headcount ) — defend your denominator choice in the log.
• Banding: create Age Band, Salary Band columns (PQ or DAX — justify), then discover: overtime × department × salary band matrix with conditional heat.
• A "Key influencers" visual on Attrition + a decomposition tree (Income → Department → Role). When AI visuals mislead, note it.
• Story pages: Overview → Drivers → Employee directory (drillthrough) → Actions (text-page recommendations — dashboards that end in action get used).
• Security: dynamic RLS by Region with 3 HR leads + HR Director override who sees all (trick: role DAX with a Director flag row OR a second role).
• Ops: publish, app with 2 audiences, subscription for Monday 9 AM, sensitivity label "Confidential", usage metrics review after a week.

Stretch: predictive angle — simple logistic-ish scorebucket in DAX using satisfaction×overtime heuristics, visualized as risk tiers; or Fabric notebook to score (bonus-only). Deliverable = GitHub folder + novypro page + 6 screenshots.

🥇 PROJECT 3 — Open Independent Data (Unguided)

Pick ONE; own it end-to-end like a consultant with a real client (me — ask me anything as your "client"):

Non-negotiables: real-world mess (document 3 cleaning challenges) · a proper star even here · at least 2 advanced DAX patterns · one interactive mechanism (bookmarks/drillthrough) · published app link (or exported PDF + screenshots if no shareable license) · decision log · 1-page executive PDF summary. Defense: in your journal, answer "why this chart/measure/model choice?" for 10 random elements — that's exactly how interviews sound.

🎓 Portfolio & career assembly-line (Week 12)

1. GitHub: one repo per project (.pbip text files!) + a README.md with screenshots, problem, model diagram, DAX highlights, results.
2. novypro.com: publish PBIX demos (respect data privacy) — recruiters browse it.
3. LinkedIn: headline Power BI Developer | DAX | Data Storytelling; Featured = 3 projects; post a carousel per project (problem → model → dashboard → insight).
4. Résumé bullets with outcomes: "Built star-schema semantic model (1.3k→tested to millions-rows pattern) cutting refresh 65% via incremental refresh; 12-measure KPI layer; dynamic RLS app for 4 teams."
5. Mock client drill (do with me): I give vague requirements → you ask 6 scoping questions → propose an MVP page list → estimate 3 days. This is 50% of a real BI job.
6. Final boss — the 60-minute rebuild: from blank Desktop, rebuild Project 1's core (model + 6 measures + 1 page). Time yourself monthly; <60 min = hireable muscle memory.

Then: → Resources_Certification_Career.md to schedule PL-300 and start interviewing. You're not a beginner anymore. 🎓

⚡ DAX Cheat Sheet — Print & Pin Edition

The Two Laws

1. Measures evaluate in FILTER CONTEXT (visual rows, slicers, cross-filters).
2. Calculated columns & iterators evaluate in ROW CONTEXT (current row),
   and CALCULATE converts row context → filter context (CONTEXT TRANSITION).

House style

Measure Name =
VAR First  = ...
VAR Second = CALCULATE ( [Measure], 'Dim'[Col] = "X" )
RETURN
    DIVIDE ( First - Second, Second )      -- always DIVIDE, never "/"

Columns: Table[Column] · Measures: [Measure] (never with table name) · Comment -- · Filter dimension columns, never FILTER ( FactTable, … ).

Core aggregations & logic

Filter-context surgery

Time intelligence (marked Date table!)

YTD     = TOTALYTD ( [M], 'Date'[Date] )           -- QTD/MTD same family
LY      = CALCULATE ( [M], SAMEPERIODLASTYEAR ( 'Date'[Date] ) )
PrevMo  = CALCULATE ( [M], PREVIOUSMONTH ( 'Date'[Date] ) )
Shift   = CALCULATE ( [M], DATEADD ( 'Date'[Date], -1, QUARTER ) )
Roll12  = CALCULATE ( [M], DATESINPERIOD ( 'Date'[Date], MAX ( 'Date'[Date] ), -12, MONTH ) )
Between = CALCULATE ( [M], DATESBETWEEN ( 'Date'[Date], DATE(2025,1,1), DATE(2025,6,30) ) )
YoY %   = VAR LY = [LY measure] RETURN IF ( NOT ISBLANK ( LY ), DIVIDE ( [M] - LY, LY ) )

10 patterns that answer 90% of requests

-- Running total
RT = CALCULATE ( [M], FILTER ( ALL ( 'Date' ), 'Date'[Date] <= MAX ( 'Date'[Date] ) ) )
-- % of total / % of parent
% Tot = DIVIDE ( [M], CALCULATE ( [M], ALL ( 'Dim' ) ) )
% Par = DIVIDE ( [M], CALCULATE ( [M], ALL ( 'Dim'[Child] ) ) )
-- Rank within selection
Rk  = IF ( HASONEVALUE ( Dim[C] ), RANKX ( ALLSELECTED ( Dim[C] ), [M] ) )
-- Moving average (rows = Date days)
MA3 = AVERAGEX ( DATESINPERIOD ( 'Date'[Date], MAX ( 'Date'[Date] ), -90, DAY ), [M] )
-- New customers in period
New = VAR c = VALUES ( F[CustID] )
      VAR p = CALCULATETABLE ( VALUES ( F[CustID] ), FILTER ( ALL ( 'Date' ), 'Date'[Date] < MIN ( 'Date'[Date] ) ) )
      RETURN COUNTROWS ( EXCEPT ( c, p ) )
-- Retention (active in period & prior)
Ret = VAR p = CALCULATETABLE ( VALUES ( F[CustID] ), PREVIOUSMONTH ( 'Date'[Date] ) )
      RETURN COUNTROWS ( INTERSECT ( VALUES ( F[CustID] ), p ) )
-- Top N flag (visual filter Rk <= 5) — or TOPN for tables:
TopT = TOPN ( 5, VALUES ( 'Dim'[C] ), [M], DESC )
-- Dynamic title
Ttl = "Sales | " & COALESCE ( SELECTEDVALUE ( D[Region] ), "All Regions" )
-- Conditional color measure (use as Field value!)
Clr = IF ( [Margin %] >= 0.3, "#2E7D32", "#C62828" )
-- Semi-additive snapshot (inventory/headcount)
Cls = CALCULATE ( [M], LASTDATE ( 'Date'[Date] ) )

Visual calculations (visual-local, no model edit)

RUNNINGSUM([M]) · MOVINGAVERAGE([M], 3) · PREVIOUS([M]) / NEXT · FIRST/LAST/LOOKUP · RANGE(3) sets · axes: ROWS / COLUMNS / ROWS COLUMNS.

Emergency decoder (top errors)

Debug ritual: table visual → return VARs one by one → DAX Studio Server Timings (SE% high = good).

Format strings

"₹ #,##0" money · "0.0%" pct · "dd-MMM-yyyy" dates · dynamic: measure → Format → Dynamic (FORMAT function string computed by another measure: IF ( [M] >= 10000000, "₹ #,##0,,.0 Cr", "₹ #,##0" ) style via format-string expression).

🎓 Certification, Career & Lifelong Resources

1. Certification: PL-300 (Power BI Data Analyst Associate)

• Status: the flagship Power BI exam, active and refreshed (skills outline updated Jan 15, 2026). Always check Microsoft Learn's exam page for the latest weight split.
• Format: 40–60 questions, ~100 min, pass = 700/1000; Pearson VUE online or test center; renew free every year online. Fee ≈ ₹4,800 for India (check current pricing; discounts via student programs/Exam Replay).
• Skill areas → your course map:

• 7-day final prep plan: ① Microsoft Learn PL-300 learning paths (free) ② your cheat sheet rewrite from memory ③ MS Practice Assessment ④ weak-area labs redo ⑤ full project rebuild in 60 min ⑥ exam-day list: RLS test steps, refresh/gateway, join kinds, time-intel quirks, workspace roles ⑦ sleep.
• Next-level (later): DP-600 Fabric Analytics Engineer (Direct Lake, SQL, KQL neighborhood).

2. Your forever resource shelf

3. 60-day expert treadmill (post-course)

Mon: rebuild one community challenge · Wed: one SQLBI read + implement on ShopKart · Fri: answer 2 forum questions · Sun: portfolio polish / post. Two months of this = top-5% practitioner habits.

4. Interview question bank (answer aloud; top 30)

Foundations: PBIX contents? Import vs DirectQuery vs Direct Lake? Workflow end-to-end? Implicit vs explicit measures? Modeling: star vs snowflake · why avoid bidirectional · date table requirements · cardinality & (Blank) member · resolve many-to-many (bridge/shared dims). DAX: row vs filter context (they will ask) · context transition · CALCULATE replace vs KEEPFILTERS · ALL vs ALLSELECTED · VALUES vs DISTINCT · measure vs column · RANKX ties? · how time intelligence works · YoY% safe-guard · why totals look wrong. Performance: diagnose slow visual (PA → DAX Studio SE/FE) · shrink model 5 ways · incremental refresh setup · aggregations/composite design. Service: workspace roles · app vs link sharing · RLS static vs dynamic + USERPRINCIPALNAME · gateway modes · build permission · deployment pipelines. Scenario: "CEO wants live sales; fact has 800M rows" (composite + hybrid + aggs — walk it through). "Managers must see only their region across 10 reports" (central certified model + dynamic RLS + audiences). Behavioral: tell about a wrong number you caught; balancing pretty vs accurate; handling vague stakeholder asks.

5. Glossary (pin this page)

Aggregator/Aggregation summarized pre-computed table · App packaged content w/ audiences · Applied Steps PQ recipe · Autodetect auto relationships (turn off) · Bookmark saved view state · CALCULATE context-rewriter · Cardinality unique count per column · Composite model mixed Import+DQ · Context transition row→filter via CALCULATE · DAX formula language · Date table marked continuous calendar · Direct Lake Fabric delta-source mode · DirectQuery live-source mode · Drillthrough filtered detail page · Fabric unified data platform home of Power BI · Fact/Dimension events vs descriptors · Field parameter user-switchable fields · Filter context active filters at eval · Gateway on-prem refresh bridge · Incremental refresh partition-based partial loads · Iterator (X-functions) row-by-row then aggregate · Lineage view dependency map · M Power Query language · Measures table _Measures home trick · OLS object-level security · Pareto cumulative 80/20 · pbip project text format (Git-able) · Power Query ETL engine · Q&A natural-language visual · RLS row-level security · Semantic model the published model artifact · Snowflake normalized dims (avoid) · Star schema dims around fact (GOAL) · TMDL model-as-code view · USERELATIONSHIP activate inactive rel · VertiPaq Power BI's columnar engine · Visual calculation visual-local DAX · XMLA endpoint model scripting port.

6. Keyboard shortcuts that save hours

Ctrl+D duplicate visual · Alt+Shift+F10 move focus · Ctrl+G/U group/ungroup · Arrow keys nudge (with Ctrl = 1px? use format pane precision) · Ctrl+Enter commit DAX new-line in formula bar (Shift+Enter for line break) · DAX Studio: F5 run · Desktop: Ctrl+S constantly 🙂 · Publish: Ctrl+Alt+P? — production habit: keep one master file versioned in Git instead of _final_v7_REAL.pbix.

7. My office hours 🤝

Stuck on a lesson, want a deeper dive on any topic, need your DAX reviewed, want another messy dataset generated, or a mock interview? Just ask me in this chat — paste your formula/model description and I'll debug it with you like a senior reviewing a junior's PR.

You started with zero. By the time you finish Module 09 and the 60-day treadmill, you'll interview like someone with two years of experience — because you'll have built the work to prove it. — Your tutor

Module 10 — Power BI for Finance: P&L, Variance & Balance-Sheet DAX

Finance reporting is Power BI's most-paid-for use case. P&Ls have rules (signs, subtotals), balances have rules (point-in-time), and variance has rules (% + bridge). Learn them and you're dangerous in FP&A hiring.

🎯 Objectives

Model a GL for reporting · signed-amount conventions · semi-additive balance measures (LASTDATE) · Actual vs Budget variance · waterfall bridges · finance-grade page design.

📘 10.1 Finance shapes: flows vs stocks

Mistaking stocks for flows is the #1 finance-reporting bug. Rule: any "balance" gets the LASTDATE treatment.

📘 10.2 Model for the GL data (408 rows, 2024–25)

Date (marked) 1──* Finance_GL (MonthStart, AccountCode, AccountName, Statement, Type, Amount)
Date 1──* Finance_Budget (MonthStart, AccountCode, AccountName, Type, Budget)

Sign convention chosen for you: income positive, costs positive in data → reporting measures apply signs:

GL Total = SUM ( Finance_GL[Amount] )
Revenue = CALCULATE ( [GL Total], Finance_GL[Type] = "Revenue" || Finance_GL[Type] = "Other Income" )
COGS = CALCULATE ( [GL Total], Finance_GL[Type] = "COGS" )
OpEx = CALCULATE ( [GL Total], Finance_GL[Type] = "OpEx" )
Gross Profit = [Revenue] - [COGS]
Operating Income = [Gross Profit] - [OpEx]
Margin % = DIVIDE ( [Operating Income], [Revenue] )

📘 10.3 Balance-sheet (semi-additive) measures

Balance = 
CALCULATE ( SUM ( Finance_GL[Amount] ),
    Finance_GL[Statement] = "B/S",
    LASTDATE ( 'Date'[Date] ) )
Cash Balance = CALCULATE ( [Balance], Finance_GL[AccountName] = "Cash" )
Working Capital = CALCULATE ( [Balance], Finance_GL[AccountName] IN { "Cash", "Accounts Receivable", "Inventory" } )
                - CALCULATE ( [Balance], Finance_GL[AccountName] = "Accounts Payable" )
Current Ratio = DIVIDE ( [WC Assets], [WC Liabilities] )

LASTDATE grabs month-end within whatever the slicer spans: quarter → last month of quarter → correct stock logic automatically.

📘 10.4 Actual vs Budget (the FP&A heartbeat)

Budget Total = SUM ( Finance_Budget[Budget] )
Budget Revenue = CALCULATE ( [Budget Total], Finance_Budget[Type] = "Revenue" )
Variance = [Revenue] - [Budget Revenue]
Variance % = DIVIDE ( [Variance], [Budget Revenue] )
Fav/Unfav = IF ( [Variance] >= 0, "▲ Favourable", "▼ Adverse" )
Var Color = IF ( [Variance] >= 0, "#2E7D32", "#C62828" )   -- conditional-format via Field value

Bridge it with a Waterfall: Budget → Volume → Price/Mix → Cost → Actual (one waterfall = a CFO's favorite picture). Use Month on axis, Variance % data labels, colored by measure.

📘 10.5 Finance page anatomy (CFO-grade)

Header: "Q3 FY26 · Operating income ₹57.2M vs budget (+3.1%)" (dynamic title measure!) · KPI row: Revenue, Gross Margin %, Operating Income, Cash Balance (all with vs-budget chips) · Matrix P&L: Statement→Type→AccountName hierarchy, Actual/Budget/Var/Var% columns, Var% conditional color · Waterfall bridge · Line: revenue trend Actual vs Budget two lines · Cards: Working Capital + Current Ratio · slicers: Year/Quarter minimal.

🧪 LAB 10 — The ShopKart Finance Pack (90 min)

1. Import Finance_GL.csv, Finance_Budget.csv; build/mark Date table; relate via MonthStart (many-to-1).
2. Type checks; create _Fin measure folder; write all §10.2–§10.4 measures (variables + comments, folder discipline).
3. P&L matrix (hierarchy + 4 value columns with Var Color on Var %); B/S row of cards using [Balance] variants — prove to yourself that a quarter filter returns month-end, not sum.
4. Waterfall: Category = Type (Revenue→OpEx order via Sort-By), Value = signed income measure; then Budget-vs-Actual bridge.
5. Line chart Actual vs Budget Revenue by month; MoM variance column chart colored by sign.
6. Executive page assembled per §10.5 + mobile layout + tooltip page for account drill.
7. Stretch: scenario measure — Scenario Rev = [Revenue] * (1 + WhatIf[Value]) via Modeling → New parameter.

💪 Exercises

1. Compute Rolling 3M Revenue and Run-rate (annualized L3M) measures.
2. Why does SUM(Balance amounts) across a quarter lie? Write the DAX that proves LASTDATE correctness in a month(s) matrix.
3. DSO = DIVIDE(AR Balance, Revenue) * Days in period — implement DaysInPeriod = COUNTROWS('Date') variant.
4. Duplicate tab "Dept P&L": add conditional-format data bars to Var%; which expense line is most off-plan in 2025?
5. Journal: how would multi-entity consolidation change the model? (Entity table + keys — preview of P5 lab.)

✅ Selected answers

• Run-rate: DIVIDE ( [L3M Revenue], 3 ) * 12 — guard for incomplete first quarter with variables.
• DSO: DIVIDE ( CALCULATE ( [Balance], AccountName="Accounts Receivable" ), [Revenue] ) * COUNTROWS ( 'Date' ).

❓ Quiz

1. Why LASTDATE for balances and not SUM over time? 2. What visual bridges Budget → Actual? 3. Variance % formula sign for costs: favourable means…? 4. What does the what-if parameter generate behind the scenes?

✅ Answers

1. Balances are stocks at a point in time; adding months double-counts the same money. LASTDATE returns the period-end stock correctly at any granularity.
2. The waterfall chart.
3. Costs: Actual < Budget is favourable — sign logic flips vs revenue; handle per-Type or present absolute with labels.
4. A GENERATESERIES table + selected-value measure — a slicer the user drives.

✅ Mastery checklist

• GL + Budget modeling with correct granularity intuition
• Semi-additive balances verified at month/quarter/year
• Variance pack: Actual/Budget/Var/Var% + sign-aware colors + waterfall
• Screenshot → portfolio (finance pack = strongest hiring asset)

Next: PQ_05_Finance.md — build the finance data engine this module consumed.

Excel 01 — Foundations: Interface, Data Entry & Formatting

Excel is where 90% of the world's data work actually happens. Master it and Power BI feels 2x easier — they share the same data brain.

🎯 Objectives

Navigate Excel with confidence · enter and format data professionally · understand cell references (the #1 beginner skill) · finish your first clean ShopKart workbook.

📘 1.1 Why Excel, and how it pairs with Power BI

Excel = flexible grid for analysis; Power BI = powerful engine for modeled, shareable dashboards. Both use Power Query for cleaning (Excel → Data → Get & Transform Data). Typical pro workflow: clean & shape in Excel/Power Query → model & visualize in Power BI. Learn Excel first = shortcut to everything.

📘 1.2 The interface map (2-minute tour)

Workbook = the file (.xlsx), worksheet = each tab, cell = one box (A1), range = group (A1:D10).

📘 1.3 Data types — what Excel actually stores

Leading apostrophe '00123 forces text. Green triangle = Excel warning you (e.g., "number stored as text").

📘 1.4 Entering data like a pro

• Enter = confirm & move down · Tab = confirm & move right · Ctrl+Enter = fill all selected cells with same value
• Fill Handle (bottom-right square): drag to copy or extend series — Jan → Feb, Mar…; 1 → 2, 3 (hold Ctrl for copy-instead-of-series)
• Flash Fill (Ctrl+E): type one example of the pattern in the next column → Ctrl+E → Excel fills the rest (splitting names, combining codes — feels like magic)
• AutoSum (Alt+=): sums the column above instantly
• Ctrl+Shift+Arrow = select to edge of data · Ctrl+Home = top of sheet

📘 1.5 Formatting for humans

• Number formats (Ctrl+1): Currency (₹ #,##0), Percentage, Date dd-mmm-yyyy, thousands separators. Format ≠ value: 0.05 formatted as % shows 5%.
• Format Painter (paintbrush) copies formatting.
• Cell Styles / Table styles for consistent look; avoid manual rainbow chaos.
• AutoFit column width: double-click the column-edge; fix ####### (= column too narrow, not an error).

📘 1.6 The reference system — the most important 10 minutes in Excel

F4 key cycles A1 → $A$1 → A$1 → $A1 while editing. Rule of thumb: lock cells holding assumptions (tax rate, target price) so one copy-drag works for 1,000 rows.

🧪 LAB 1 — Your first clean workbook (30 min)

1. Open Excel → Blank workbook → Data → Get Data → From Text/CSV → datasets/Sales.csv → load. (Alternative: File → Open the CSV directly.)
2. Click any data cell → Ctrl+T → "My table has headers" ✔. Your range is now a real Table (banded, filterable, auto-expanding). Name it tblSales (Table Design tab).
3. Format UnitPrice column: select → Ctrl+1 → Currency → Symbol ₹ → ₹ #,##0. Same for a new column we'll add.
4. New column: in first empty header cell type Revenue, Enter. In first data cell type:

=[@Quantity]*[@UnitPrice]*(1-[@Discount]) — watch the whole column auto-fill (structured references!). Format as ₹.

5. Freeze the top row: View → Freeze Panes → Freeze Top Row.
6. Table Design → check Total Row → set Revenue total to Sum.
7. Home tab: make header row bold if not, give Sheet1 a name: Sales. Ctrl+S → Save As ShopKart.xlsx. (.xlsx keeps formatting & tables; .csv loses everything.)

💡 You just did in ~15 minutes what managers pay analysts for: trusted, formatted, self-updating numbers.

💪 Exercises

1. Use Flash Fill to extract the last 5 characters of OrderID into a new column OrderNum.
2. Add a Discount % column formatted as percent; verify one row by mental math.
3. In an empty cell: Alt+= on Revenue; then change it to Average via the dropdown.
4. Practice F4: write =B2*$E$1 referencing a tax cell E1=0.18 for a GST column; copy down 10 rows.
5. Jump practice: press Name Box, type Z500, Enter; then Ctrl+Home.

❓ Quiz

1. Difference between .csv and .xlsx? 2. What does ####### mean and how do you fix it? 3. Why are dates secretly numbers, and what does that enable? 4. What does F4 do while editing a formula?

✅ Answers

1. CSV = plain text, one sheet, no formatting/formulas preserved; XLSX = full workbook (multiple sheets, formats, tables, charts).
2. Column too narrow to display the number — widen (double-click edge).
3. Dates = serial days since 1-Jan-1900, so =A2-A1 gives days between dates, +7 adds a week.
4. Cycles the reference through $A$1 → A$1 → $A1 → A1 (absolute/mixed locking).

✅ Mastery checklist

• I can move around without touching the mouse much
• Tables via Ctrl+T + structured refs feel natural
• I know when to lock with $ (and use F4)
• Lab 1 redone from a blank workbook

Next: Excel_02_Formulas_Core_Functions.md — the formula engine room.

Excel 02 — Formulas & Core Functions

Functions are Excel's vocabulary. Master ~25 of them and you out-analyze people with "10 years of experience".

🎯 Objectives

Write correct formulas fast · logic with IF family · conditional aggregation (SUMIFS) · lookups (XLOOKUP/VLOOKUP) · bulletproof error handling.

📘 2.1 Formula anatomy

Every formula starts with =. =B2*C2 same-cell math · =SUM(D2:D10) function over a range. Precedence (like school math): () → ^ → * / → + -. Comparisons > = < >= <= <> return TRUE/FALSE. Edit with F2 (or double-click cell).

📘 2.2 The essential 12 (memorize cold)

SUM AVERAGE MIN MAX COUNT (numbers only) COUNTA (non-empty) COUNTBLANK MEDIAN LARGE(range,n) SMALL(range,n) ROUND(x,2) TODAY() Tip: LARGE(range,2) = 2nd biggest — instant Top-N analysis without sorting.

📘 2.3 Logic family

=IF(B2>=50000,"High","Low")
=IFS(B2>=50000,"High", B2>=10000,"Mid", TRUE,"Low")     -- cleaner than nested IFs
=IF(AND(B2>1000,C2="West"),"VIP","")                    -- AND(...)  OR(...)

Nested IFs work (=IF(x,"a",IF(y,"b","c"))) but IFS keeps you sane.

📘 2.4 Conditional aggregation — business favorite

=SUMIF(Region,"West",Sales)                    -- one condition
=SUMIFS(SalesRange, Region,"West", Category,"Electronics")   -- many ANDs
=COUNTIFS(Region,"North", Qty,">=3")
=AVERAGEIFS(...)

Syntax law: SUMIF = (criteria_range, criteria, sum_range); SUMIFS = (sum_range first, then pairs). Wildcards: "Lap*" starts with, *mouse* contains, "?" single char.

📘 2.5 Lookups — fetching data across tables

XLOOKUP (Excel 365/2021 — the modern king):

=XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode])
=XLOOKUP([@ProductID], tblProducts[ProductID], tblProducts[Category], "Unknown")
=XLOOKUP(code, Products[ID], Products[Price], , -1)   -- next-smaller match (price tiers)

Exact match by default (VLOOKUP's biggest trap gone), looks left or right, survives inserted columns.

VLOOKUP (legacy, still everywhere):

=VLOOKUP(value, table, col_index_number, FALSE)   -- FALSE = exact match. ALWAYS use FALSE unless ranges/tiers.
=RANK-equivalents only with TRUE on sorted data.

Know both; default to XLOOKUP when available.

📘 2.6 Error-proofing

🧪 LAB 2 — An analyst's summary sheet (45 min)

1. In ShopKart.xlsx: also import Products.csv and Customers.csv as Tables: tblProducts, tblCustomers.
2. In tblSales, add Category: =XLOOKUP([@ProductID],tblProducts[ProductID],tblProducts[Category],"?")
3. Add Region (two-hop lookup!): =XLOOKUP(XLOOKUP([@CustomerID],tblCustomers[CustomerID],tblCustomers[Region]),tblCustomers[Region],tblCustomers[Region]) — simpler: nested XLOOKUP fetches region via customer. (If your Excel is older: =VLOOKUP([@CustomerID],tblCustomers[#All],6,FALSE).)
4. New sheet Summary. In B2: =SUMIFS(tblSales[Revenue],tblSales[Region],"West"); build a small grid: Regions down, Categories across, formula combining SUMIFS with $ locked ranges and row/col labels. One formula, dragged everywhere — this is why $ matters.
5. Add YoY-lite: =COUNTIFS(tblSales[OrderDate],">=1/1/2025") (mind your system's date format) — criteria on dates work with "& comparisons.
6. Wrap lookup columns in IFNA to show "Unmapped" instead of #N/A.
7. Spot-check 3 numbers manually against a filtered view (always validate!).

💪 Exercises

1. =COUNTIFS(tblSales[Quantity],">=5",tblSales[Discount],0) — how many full-price bulk orders?
2. Average revenue of Electronics orders in South.
3. =LARGE(tblSales[Revenue],1) vs =MAX(...) — same? Then find the 2nd largest.
4. Write with IFS: band AvgDiscount into Zero (<0.01), Low (<0.08), High (else).
5. Legacy drill: rebuild the Category column with VLOOKUP; explain in your journal one way it can silently break that XLOOKUP can't (hint: inserting a column in tblProducts).

✅ Selected answers

• Ex 2: =AVERAGEIFS(tblSales[Revenue],tblSales[Category],"Electronics",tblSales[Region],"South")
• Ex 5: VLOOKUP's col_index is a hard-coded number — insert a column in tblProducts and the lookup silently returns the wrong column. XLOOKUP references the column itself.

❓ Quiz

1. SUMIF vs SUMIFS argument order? 2. Why is XLOOKUP's default safer than VLOOKUP's? 3. "Lap*" means what in criteria? 4. Show the IFERROR pattern for A/B.

✅ Answers

1. SUMIF: criteria_range, criteria, sum_range(last). SUMIFS: sum_range first, then range/criteria pairs.
2. XLOOKUP is exact-match by default; VLOOKUP defaults to approximate (dangerous unless you write FALSE).
3. Wildcard: any text starting with "Lap".
4. =IFERROR(A2/B2,"") (or a friendly fallback label).

✅ Mastery checklist

• SUMIFS grid built with locked ranges (drag = correct everywhere)
• Two-hop lookup understood (Sales→Customers→Region)
• I default to XLOOKUP and IFNA for hygiene

Next: Excel_03_Data_Tools.md — sort, tables, text & dates, validation, conditional formatting.

Excel 03 — Data Tools: Tables, Text, Dates, Validation & Conditional Formatting

Raw data is a swamp. These tools are your machete — the Excel equivalents of Power Query's cleaning power.

🎯 Objectives

Sort/filter fluently · exploit Excel Tables fully · master 15 text & date functions · enforce clean input with Data Validation · make patterns pop with Conditional Formatting · clean the messy file — the Excel way.

📘 3.1 Sort, Filter, and the Table superpowers

• Filter toggle: Ctrl+Shift+L — dropdowns with search box ("contains 'lap'").
• Multi-level sort: Data → Sort → add levels (Region, then Revenue ↓). Custom sort for "High, Mid, Low" logic.
• Tables (Ctrl+T) — always use them: auto filters, banded rows, structured references (tblSales[Revenue] — readable & immune to row counts), auto-expansion (paste new rows at the bottom and formulas/formatting extend), Total Row (Table Design tab), slicers for tables.

📘 3.2 Text functions — the cleanup crew

TRIM(x)          -- kills extra spaces (join-breaker #1)
CLEAN(x)         -- removes invisible characters
UPPER/LOWER/PROPER(x)
LEFT/RIGHT/MID(text,n) / LEN(x)
CONCAT(a,b)  or  a&b
TEXTSPLIT(text,",")                -- 365: split to cells
TEXTJOIN(", ",TRUE,range)          -- 365: combine
SUBSTITUTE(text,"₹","")            -- targeted replace (vs REPLACE by position)
FIND("x",text) / SEARCH            -- position of a char (drives MID tricks)

Pattern: =TRIM(PROPER(" laptop pro 14 ")) → Laptop Pro 14. Combine: =LEFT(A2,FIND("-",A2)-1) → text before the dash.

📘 3.3 No-code rescue tools

• Remove Duplicates (Data tab): select key column first (e.g., OrderNo) to dedupe whole rows.
• Text to Columns (Data tab): split delimited text or force column types — fixes stubborn dates.
• Flash Fill (Ctrl+E) for pattern-based splits/merges — zero formulas.
• Find & Replace (Ctrl+H) with */? wildcards and "Match entire cell contents".

📘 3.4 Dates — serial-number ninja moves

TODAY() NOW()            -- volatile (recalc always)
YEAR/MONTH/DAY(date)
EOMONTH(date,0)          -- last day of month (+n/-n for other months)
NETWORKDAYS(start,end)   -- business days
DATEDIF(start,end,"Y")   -- full years/age ("M","D" also) — undocumented since Excel 2000, still works
date+7                   -- a week later (dates are numbers!)

Convert text-dates: =DATEVALUE("21-Jul-2026") or Text to Columns → Date (choose DMY for Indian formats!).

📘 3.5 Data Validation — guardrails on input

Data → Data Validation → Allow: List (type North,South,East,West or point at a range), Whole Number (min/max), Date, or Custom formula (=LEN(A2)>3). Add Input Message (help text) + Error Alert (block vs warn). Then Data → Circle Invalid Data to audit. Dropdowns = no more "West", "west", "WEST".

📘 3.6 Conditional Formatting — instant insight

Home → Conditional Formatting: Data Bars, Color Scales, Icon Sets, Top/Bottom rules, Highlight Duplicates. Power move — New Rule → Use a formula: =$D2="" (turn whole row amber when a price is missing) or =AND($E2>TODAY()-30,$E2<>""). Rules evaluate top-to-bottom; Manage Rules to reorder/Stop-if-True.

🧪 LAB 3 — Clean Messy_Sales_Raw.csv the Excel way (45 min)

(Same villain file from Power BI Module 02 — different weapon.)

Appeal: import Data → From Text/CSV → load to sheet → Ctrl+T → name stg_MessySales.

1. Product names: helper column =TRIM(PROPER([@Product])); copy → Paste Special Values over itself; delete original, rename.
2. Region chaos: =PROPER(TRIM([@Region])) fixes case+spaces; then Data Validation list North, South, East, West + Circle Invalid Data to prove none left.
3. Price: =VALUE(SUBSTITUTE(SUBSTITUTE([@Price],"₹",""),",","")) → errors on the "error"/N/A rows? IFERROR→ blank, then filter blanks and delete those rows (document the choice!).
4. Qty: same approach; Fill blanks with 1 (business decision — journal it).
5. Dates: select column → Text to Columns → Delimited → Next → Next → Date: DMY → Finish. The 25-10-2024 vs 2024-10-08 mix — inspect: which ones misconverted? (Hint: ISO ones. Fix by correcting those rows with the same T2C on a copy, or Power Query instead — note why PQ wins for mixed formats.)
6. Duplicates: Data → Remove Duplicates on OrderNo. (148 → ~140.)
7. Add LineTotal = [@Qty]*[@Price], format ₹, Save.

Compare in your journal: Excel-vs-PowerQuery — which records a replayable recipe for next month's file?

💪 Exercises

1. Extract the numeric part of ORD-10018 with MID+FIND (no helper text-to-columns).
2. =DATEDIF(JoinDate,TODAY(),"Y") — add customer tenure to tblCustomers.
3. Data Validation: restrict Quantity to whole numbers 1–10; add an input message.
4. Conditional-format Revenue with 3-color scale; then a formula rule highlighting the entire row when Discount > 0.15.
5. Sort tblSales: Month ↑ (create Month col with =TEXT([@OrderDate],"yyyy-mm")), then LineTotal ↓.

✅ Selected answers

• Ex 1: =MID(A2,FIND("-",A2)+1,99)*1 (the *1 coerces text → number).
• Ex 4 formula rule (select full range, rule applies to row 2 top): =$F2>0.15 — lock the column ($F), free the row.

❓ Quiz

1. Three Table superpowers. 2. TRIM vs CLEAN? 3. Why does TEXT(date,"yyyy-mm") sort correctly but "mmm-yyyy" doesn't? 4. In a conditional-format formula for whole-row highlighting, what do you lock?

✅ Answers

1. Structured references, auto-expansion, total row (+ built-in filters/formatting).
2. TRIM strips spaces; CLEAN strips non-printable characters — often chained together.
3. "yyyy-mm" alphabetizes chronologically (2025-01 < 2025-10); month names sort A-Z ≠ time order.
4. The column (=$F2) — so every cell in the row checks the same F-cell as the rule walks down rows.

✅ Mastery checklist

• Messy file cleaned twice: once with formulas, once with Ctrl+E/Replace/Validation
• I can build a validation dropdown + audit with Circle Invalid
• Formula-based conditional formatting with $-locking works first try

Next: Excel_04_Pivot_Charts_Analysis.md — PivotTables: Excel's crown jewel.

Excel 04 — PivotTables, Charts & What-If Analysis

If Excel had one feature to rule interviews, it's the PivotTable — drag, drop, and a 5,000-row dataset confesses in seconds.

🎯 Objectives

Build and format PivotTables confidently · Show-Values-As % tricks · slice & dice with slicers/timelines · pick and polish charts · answer "what if?" with Goal Seek and Data Tables.

📘 4.1 PivotTables — the 60-second concept

A PivotTable summarizes flat data by dimensions: drag Region→Rows, Revenue→Values = instant totals. Wells: Rows, Columns, Values, Filters. Insert → PivotTable (source = a Table like tblSales; New Worksheet recommended). Rules of good sources: one header row, no merged cells, no blank rows/columns, one row per record — your Tables already qualify.

⚠️ Refresh: Pivots cache data — after editing the source, Alt+F5 or right-click → Refresh. (PivotTable Options → Data → "Refresh on open" helps forgetful humans.)

📘 4.2 Value Field Settings — where the magic hides

Click the dropdown on a value field → Value Field Settings:

• Summarize by: Sum / Count / Average / Max / Min.
• Show Values As: % of Grand Total, % of Parent (category share inside Region!), % of Column/Row Total, Difference From (e.g., vs previous month → instant MoM delta), Running Total In, Rank. This one tab replaces pages of formulas.

📘 4.3 Grouping, Slicers, Timelines

• Date grouping: right-click a date in the pivot → Group → Years/Quarters/Months (modern Excel auto-groups date fields — right-click → Ungroup to remove).
• Number grouping: band ages/incomes (0–10k, 10–20k…).
• Slicers (Insert → Slicer): clicky filter buttons for any field; Timeline = slicer for dates. Report Connections lets one slicer drive multiple pivots → dashboard feel.
• Design touches: Report Layout → Tabular (Excel-pro look), repeat item labels, grand totals on/off.

📘 4.4 Charts that tell the story

• PivotCharts: select pivot → Insert → PivotChart. Combo charts: two metrics (column Revenue + line Margin %) via Change Chart Type → Combo → Secondary Axis for the %.
• Chart chooser recap (same logic as Power BI track): comparison → bar/column · trend → line · share → stacked/bar (pie ≤5 slices) · KPI vs target → column + marker.
• Sparklines (Insert → Sparklines): tiny in-cell trend lines — perfect for a Summary sheet.
• Polish rules: direct labels > legends · kill gridlines/borders · one accent color · title = takeaway.

📘 4.5 What-If tools (Data → What-If Analysis)

🧪 LAB 4 — ShopKart Excel Dashboard (60 min, on ShopKart.xlsx from Lab 2)

1. Insert → PivotTable from tblSales (with your Category & Region cols) → new sheet Dashboard.
2. Pivot A: Rows = Category → Values = Revenue (Sum, ₹ format) + Revenue again → second field → Show Values As % of Grand Total. Sort desc.
3. Pivot B: Rows = OrderDate (grouped Years + Months), Values = Revenue → add Revenue again → Show Values As Difference From → OrderDate → (previous) = MoM change. Format green/red.
4. Insert a Slicer for Region + a Timeline for OrderDate; wire both to both pivots (Report Connections).
5. Insert PivotChart (Combo): Column = Revenue by Month, Line = Running Total (Show Values As → Running Total In).
6. New sheet Spark: 5 product rows, in-cell sparkline of monthly revenue (source via pivot or GETPIVOTDATA).
7. Goal Seek: on a scratch cell chain TargetRev = Price*Qty*0.9 → set TargetRev to 1,000,000 by changing Qty. Record the answer.
8. Format the Dashboard: title, KPI cards (big-number cells referencing GETPIVOTDATA or cube of pivots), hide gridlines (View → uncheck Gridlines).

💪 Exercises

1. Pivot: Category rows, Margin buckets columns (create a helper col first), values = % of Parent Row. Which category is discount-bleeding?
2. Rank each region's products: value field → Show Values As → Rank Largest to Smallest (base field ProductName).
3. Two-input Data Table: Revenue across Price (rows: 700,800,900…) × DiscountRate (cols: 0%,5%,10%,15%).
4. Turn the Category pivot into a normalized table again? Trick question — unpivoting needs Power Query (remember Module 02 & Excel_05!). Write the why in your journal.
5. Customize a slicer: 2 columns, style match, hide header.

❓ Quiz

1. Why must you refresh a pivot (and shortcut)? 2. Difference From + (previous) gives you…? 3. When do you need a Secondary Axis on a combo chart? 4. Goal Seek changes ___ to make ___ equal a goal.

✅ Answers

1. Pivots cache; source edits don't flow in until Refresh (Alt+F5 / right-click → Refresh).
2. The period-over-period delta (MoM/QoQ/YoY growth) with zero formulas.
3. When two series have very different scales (₹ vs %), else the small-scale line looks flat on zero.
4. One input cell … one formula cell to the desired value.

✅ Mastery checklist

• % of Parent and Difference From used without fear
• One slicer drives two pivots
• I can defend my chart choice for any question
• Dashboard screenshot saved to portfolio

Next: Excel_05_Advanced.md — INDEX/MATCH, dynamic arrays, Power Query in Excel, macros.

Excel 05 — Advanced: INDEX/MATCH, Dynamic Arrays, Power Query & Macros

This module is the bridge from "Excel user" to "person others call". It also connects directly to Power BI — same Power Query engine, refresh-and-done mindset.

🎯 Objectives

Lookup freedom with INDEX+MATCH · modern dynamic-array formulas (365) · Get & Transform (Power Query) inside Excel · record your first macro · professional workbook hygiene & protection.

📘 5.1 Defined Names — say goodbye to $F$2:$F$99 nonsense

Formulas → Name Manager → New: TaxRate = Sheet1!$E$1, PriceList = a Table column. Then formulas read =Qty*Price*TaxRate. Names can hold constants, ranges, even formulas (dynamic named range: =OFFSET — with caution). Scopes: Workbook vs one sheet. Keyboard: Ctrl+F3.

📘 5.2 INDEX + MATCH — the lookup the pros trust

=INDEX(return_column, MATCH(lookup_value, lookup_column, 0))
=INDEX(tblProducts[ProductName], MATCH("P7", tblProducts[ProductID], 0))
-- Two-way matrix lookup:
=INDEX(B2:Z50, MATCH(city, A2:A50, 0), MATCH(month, B1:Z1, 0))

MATCH types: 0 exact · 1 largest ≤ value (ascending data — price tiers) · -1 smallest ≥ (descending). Why pros love it: looks any direction, no column-count fragility, cheaper on big sheets than VLOOKUP. (If you have 365: XLOOKUP does all of this alone — know both, interviews ask both.)

📘 5.3 SUMPRODUCT & friends (pre-365 power tricks)

=SUMPRODUCT((Region="West")*(Category="Electronics")*Revenue)   -- SUMIFS with math/logic inside
=SUMPRODUCT((MONTH(OrderDate)=10)*Qty)                           -- conditions SUMIFS can't express

COUNTIFS covers most counting; SUMPRODUCT covers formula-logic conditions.

📘 5.4 Dynamic Arrays (Microsoft 365) — a new Excel

One formula → results spill into many cells (blue outline; # refers to the spill; #SPILL! = blocked):

=UNIQUE(tblSales[Region])                                  -- list of regions, auto-updates
=SORT(UNIQUE(...)) 
=FILTER(tblSales, tblSales[Revenue]>100000, "No big orders")   -- rows matching, live
=SORTBY(tblProducts[ProductName], tblProducts[UnitPrice], -1)
=SEQUENCE(12,1,DATE(2025,1,1),31)                          -- dynamic month starts
=LET(r,[@[Revenue]], IF(r>50000,"⭐","-"))                 -- name inline values: readable formulas
=LAMBDA(x, x*1.18)(100)                                    -- reusable logic via Name Manager: =AddGST(100)

Also: XLOOKUP, XMATCH, SEARCHB-free splits (TEXTSPLIT, TEXTBEFORE/AFTER, TOCOL, TAKE, VSTACK). Compatibility: older Excel shows @ (implicit intersection) and won't spill — check your audience's version.

📘 5.5 Power Query in Excel — your Power BI head start

Data → Get & Transform Data = the same engine you learned in Power BI Module 02: connect CSV/Folder/Web/SQL, Applied Steps, merges, unpivots, M. Workflow: Get Data → From Text/CSV → Transform Data (same editor!) → Close & Load To → Table/PivotTable Report/Connection Only. Refresh All (Ctrl+Alt+F5) re-runs everything. Rule: if a cleaning task will repeat monthly, build it in Power Query, not by hand.

📘 5.6 Macros & VBA — first steps

• View → Macros → Record Macro (tick "Use Relative References" first!) → do your formatting clicks → Stop. Run it on new data = same steps instantly.
• View the code (Alt+F11): it's writing VBA for you (Sub FormatReport() ... End Sub) — nudge values, save as .xlsm (macros can't live in .xlsx), assign to a shape/button (Insert → Shape → right-click → Assign Macro).
• Trust Center: macros run only from files you enable — never enable random macros from the internet.
• When to graduate from macros to Power Query: data-shaping → PQ; workbook actions (format, export, email) → VBA.

📘 5.7 Professional hygiene & protection

1. One Inputs/Config sheet for assumptions (named cells); formulas never hardcode numbers.
2. Tables + names everywhere; document logic with sheet README; consistent colors (locked cells grey, input cells yellow).
3. Review → Protect Sheet (allow selecting formats only; optionally unlock input cells first). Workbook protection for structure.
4. Version your files in OneDrive/SharePoint (auto-history) — the Excel counterpart of Power BI's .pbip story.

🧪 LAB 5 — The advanced trifecta (75 min)

A) Dynamic-array report (365): new sheet LiveReport:

1. B2: =LET(reg, SORT(UNIQUE(tblSales[Region])), reg) → spilling region list.
2. C2: =SUMIFS(tblSales[Revenue], tblSales[Region], B2#) → whole column answers from one formula (note the B2# spill reference!).
3. E2: =TAKE(SORTBY(tblProducts, tblProducts[UnitPrice],-1), 5) → live Top-5 premium products table.

B) Power Query bridge: Data → Get Data → queries for Sales, Products, Customers (Connection Only) → Merge Sales+Products (name pq_Fact) → Close & Load To → PivotTable Report. Refresh after editing a CSV → magic. Compare with Module 02 of the Power BI track: same steps, same M code, different home.

C) First macro: on any sheet: Record Macro FormatHeader (relative refs on) → make A1 bold, fill dark, font white, row autofit → Stop → Alt+F11 to admire/easy-edit the VBA → Save as ShopKart_Lab5.xlsm → draw a shape, Assign Macro, click. Automation unlocked.

💪 Exercises

1. Rewrite a VLOOKUP from Module 02's Lab as INDEX/MATCH; then break it deliberately (insert column in source) — explain what survived and why.
2. =SUMPRODUCT((MONTH(tblSales[OrderDate])=10)*(YEAR(tblSales[OrderDate])=2025)*tblSales[Quantity]) — what does it give? Verify with a filtered pivot.
3. Build a price-tier lookup with MATCH type 1.
4. LET-ify any ugly formula from earlier: name intermediate values, shave 30% of length.
5. Record a macro that exports the active sheet to PDF (hint: use ExportAsFixedFormat — find it via recording).
6. Protect the Summary sheet allowing edits only on the yellow input cells.

✅ Selected answers

• Ex 2: total units sold in October 2025.
• Ex 1: INDEX/MATCH survives inserted columns (references are explicit columns, not a counted offset); VLOOKUP silently shifts.

❓ Quiz

1. MATCH's three match types and when type 1 is useful. 2. What does # after a cell reference mean in 365? 3. Two reasons to choose Power Query over manual cleaning in Excel. 4. What file type must macros live in?

✅ Answers

1. 0 = exact · 1 = largest value ≤ lookup (ascending ranges → tiers/bands) · -1 = smallest ≥ lookup (descending).
2. The entire spilled result of that cell's dynamic-array formula (e.g., B2#).
3. It's a recorded, refreshable recipe (no redo-by-hand next month) and far more powerful/auditable (applied steps, merges, M).
4. .xlsm (or personal.xlsb) — .xlsx strips macros.

✅ Mastery checklist

• INDEX/MATCH and XLOOKUP both fluent
• One solved problem each via dynamic arrays, Power Query, and a macro
• Workbook hygiene: names, no hardcodes, protected output
• I see the Excel ↔ Power BI bridge clearly (same Power Query!)

Next stop: back to the Power BI track (Module_02_Power_Query.md) — or take PL-300/common Excel interview questions in Resources_Certification_Career.md. 🎓

E6 · Excel for Finance: Loans, NPV/IRR & Financial Modeling

Finance teams live in Excel. These functions and patterns are what analysts in banks, FP&A and CA firms use every single day — and what interviews test.

🎯 Objectives

Time-value-of-money functions · build a loan amortization schedule · value projects with NPV/IRR/XIRR · depreciation & ratios · model best practices used in real financial models.

📘 F1. The time-value toolkit (memorize signatures)

💡 Sign convention: money out = negative. PMT(9.5%/12, 60, -5000000) → ₹1,05,012 (L1's EMI).

📘 F2. Loan amortization — the classic build

Per row (period n): EMI = PMT(...) (locked with $) · Interest = IPMT(...) or Balance_prev * rate/12 · Principal = EMI - Interest (or PPMT) · New Balance = Balance_prev - Principal · checks: final balance → 0. Add CUMIPMT(rate/12,60,-pv,1,n,0) for "total interest till now" — the number that shocks borrowers.

📘 F3. Valuing a project: NPV, IRR, and their date-aware siblings

=NPV(12%, B4:B9) + B3          -- B3 = initial outlay (negative). NPV() pockets flows END of period 1+
=IRR(B3:B9)                    -- rate where NPV = 0 (needs one sign change)
=XNPV(12%, flows, dates)       -- irregular/real dates — the honest one
=XIRR(flows, dates)            -- irregular dates version of IRR

Decision rule recap: NPV > 0 → project adds value; IRR > cost of capital → accept. Common bug: including year-0 inside NPV() directly — add it separately (it's already "present").

📘 F4. Depreciation & ratio one-liners

SLN(cost, salvage, life) straight-line · DB(...) declining balance · DDB(...) double-declining. Ratios from your E4 pivot skills: Gross margin = 1 − COGS/Revenue · Current ratio = Current Assets/Current Liabilities · Debt/Equity · DSO = AR/Revenue×365 · Inventory days.

📘 F5. Financial-modeling discipline (the CA-grade checklist)

1. Blue = inputs, black = formulas, never hardcode inside formulas → one Inputs sheet with named cells.
2. One row = one period, one column = one driver; no merged cells anywhere (they break sorting/references).
3. Checks panel: Balance check (A = L + E → =IF(ABS(diff)<1,"OK","ERROR")), total checks — always visible.
4. Scenario spine: assumptions table (Best/Base/Worst) + CHOOSE or XLOOKUP to switch the whole model.
5. Document everything: a README cell per input ("Source: FY25 budget v3").

🧪 LAB E6 — Finance suite (75 min) · data: Finance_Loans.csv, provide your own project flows

1. EMI calculator: import loans as Table tblLoans → add EMI =PMT([@AnnualRatePct]/100/12, [@Years]*12, -[@Principal]) formatted ₹. Compare L1 vs L4 total interest (=EMI*n - Principal).
2. Amortization sheet (L1): rows 1–60 with n, EMI, =IPMT(...), =PPMT(...), Balance. Color inputs blue; check cell Final Balance = 0 must say OK. Line chart: balance ↓ over time; stacked columns interest vs principal (watch interest shrink).
3. Project X: initial outlay ₹40,00,000 then inflows 8L, 10L, 12L, 15L, 18L (years 1–5): compute NPV@12% (correct way: =NPV(12%, inflows) - 4000000) and =IRR(range). Decision + journal note.
4. Real dates: same flows on irregular dates in B:C → =XIRR(values, dates) vs IRR — record the difference.
5. Ratios page: finance-flavored pivot from a summary you make of Finance_GL.csv: Current ratio, Gross margin %, Debt/Equity per month.
6. Scenario spine: three revenue-growth assumptions; =CHOOSE($B$1, ...) switches model; data table over rate × term for the EMI (what-if grid).

💪 Exercises

1. Which loan costs the most TOTAL interest? Prove with CUMIPMT pattern of your own.
2. Savings goal: how much monthly SIP (PMT) to reach ₹50L in 10 years at 12% p.a. monthly compounding? (=PMT(rate/12,120,0,-5000000)? careful with signs — try until ₹50L appears.)
3. Compute payback year for Project X with a running-total column + MATCH.
4. DB vs SLN first 3 years for a ₹10L asset (7.5% salvage? pick 10%), 5-year life — chart the difference.
5. Build a "break-even units" mini-model: (Fixed Costs)/(Price − Variable Cost) with named cells and one Goal Seek check.

✅ Selected answers

• Ex 2: ≈ ₹21,522/month — =PMT(12%/12, 120, 0, -5000000) (fv positive target).
• Payback: running total crosses 0 between year columns → =MATCH(TRUE, running>=0, 0) array or helper flag column.

❓ Quiz

1. What do IPMT/PPMT split? 2. Why add year-0 outside NPV()? 3. XIRR vs IRR? 4. Blue vs black in models?

✅ Answers

1. Each EMI into its interest component and principal-repayment component (they sum to EMI).
2. NPV() discounts every flow assuming end-of-period; the initial outlay today shouldn't be discounted.
3. IRR assumes evenly-spaced periods; XIRR uses actual dates — reality is irregular.
4. Blue font = hardcoded input; black = formula — instant audit readability.

✅ Mastery checklist

• EMI + full amortization from memory, checks green
• NPV/IRR/XIRR computed and defended aloud
• One model with Inputs sheet + checks + scenario switch
• Screenshot of amortization chart → portfolio

This completes the 📗 Excel track (6 modules). Next: revisit ⚡ Power BI Module 10 (Finance) to see these numbers in a live P&L dashboard.

PQ 01 — Power Query Foundations: Connect, Load, Clean, Repeat

Power Query is the invisible engine inside Power BI and Excel. Learn it once → use it in both. This track takes you deeper than Module 02 ever could.

🎯 Objectives

Understand where Power Query lives and how it thinks · connect to any file · master the Editor's key areas · know your data types cold · reload with confidence.

📘 1.1 What Power Query really is

An ETL engine (Extract-Transform-Load) that records your clicks as Applied Steps and replays them on demand. Same engine in:

• Power BI Desktop: Home → Transform data
• Excel: Data → Get & Transform Data
• Power BI Service (Dataflows), Fabric Data Factory: cloud versions of the same brain

The magic contract: connect once → clean via steps → refresh forever. If you ever catch yourself manually fixing the same column monthly, Power Query is the answer you're ignoring.

📘 1.2 The Editor map (memorize these 7 zones)

📘 1.3 Connecting: the sources you'll meet weekly

• Files: Text/CSV, Excel Workbook (sheet or named table), JSON, XML, PDF, Folder (the pro one — see PQ 02)
• Databases: SQL Server, PostgreSQL, MySQL… (server + credentials)
• Web: https://... URL to CSV/JSON/API
• SharePoint/folders in the cloud, Azure/Fabric items
• Load dialog has two buttons of consequence: Load (to destination) vs Transform Data (open editor). Experts always Transform first — even if just to verify types.

📘 1.4 Data types are contracts, not suggestions

Each column has one type. Wrong types = broken math, dates that won't sort, merges that silently fail.

• Type conversions live in Applied Steps as Changed Type — editable via the ⚙ gear icon.
• Using Locale (right-click a column → Change Type → Using Locale): critical for Indian date/number formats (dd-MM-yyyy, 1,20,000).
• Text vs Number traps: OrderID "10018" looks fine but won't relate to numeric keys.
• Types cheat: Int64.Type, type text, type date, type datetime, Currency.Type, Percentage.Type, Logical.Type.

📘 1.5 Applied Steps thinking (the whole philosophy)

• Steps run top to bottom, every refresh → order matters (clean text before type change).
• Click a step → grid shows life at that line. Click ⚙ to edit the dialog that made it. Right-click → Rename (self-documenting steps like Fix Region Case), delete, reorder, Extract Previous (split query).
• Debug habit: when a number looks wrong, walk the steps from top, watching the grid change. You'll spot the culprit step in seconds.

📘 1.6 Load destinations & refresh

• Right-click query → Enable Load off = staging (refresh but don't store in model).
• Include in report refresh off = frozen snapshot (rare).
• Excel: Close & Load To → Table / PivotTable / Connection Only. Power BI: Close & Apply to the model.
• Refresh re-runs all queries source → steps → destination. One broken step fails its query.

🧪 LAB 1 — The 20-minute first clean (recap speed-run)

1. Power BI (or Excel) → Get Data → Text/CSV → datasets/Messy_Sales_Raw.csv → Transform Data.
2. Steps (name each one properly!): Trim+Proper Product → Trim Region → Capitalize-Each-Word Region → Replace ₹ & , in Price → type Price Whole Number → Remove Errors → type Qty → Replace Errors with null→1 (or fix first) → Change Order Date type Using Locale → Remove Duplicates on OrderNo → Split OrderNo on -.
3. View → turn ON Column quality & Column distribution. Confirm Valid = 100% where expected.
4. Rename query stg_MessySales_Clean, group it under folder stg_Staging. Close & Load… (PQ 02 will combine monthly files like this one — automatically.)

💪 Exercises

1. Import Products.csv; fix the UnitCost/UnitPrice types; count how many steps were auto-added vs yours.
2. Type experiment: deliberately change UnitPrice to Text → what breaks downstream? Delete that step to heal.
3. Rename auto steps of Lab 1 into human sentences; reload the file and narrate each step's effect.
4. Locale drill: type-ify "31/01/2025" in one query with Locale = English (India) vs English (United States). Record the difference.
5. Disable Load on a query; observe it vanish from the model view but still refresh.

❓ Quiz

1. What happens on every Refresh? 2. Why "Transform Data" before "Load" even for clean-looking files? 3. Steps you've renamed meaningfully serve what purpose? 4. What does "Using Locale" fix?

✅ Answers

1. Every Applied Step of every enabled query re-executes against current sources — cleaning replays automatically.
2. Types may be mis-detected (dates as text, numbers as text) even when data looks fine → verify/snap a Changed Type step explicitly.
3. Documentation + debugging: future-you reads Fix Region Case instead of decoding Changed Type3.
4. Culture-specific parsing: day-first dates and , thousands in Indian data — US locale mangles them.

✅ Mastery checklist

• Editor's 7 zones without thinking
• Every step named; I can debug by clicking steps
• Staging vs loaded decision made deliberately
• Lab 1 repeated in both Excel and Power BI

Next: PQ_02_Shaping_Combining.md — pivots, joins, appends, and the legendary folder-combine.

PQ 02 — Shaping & Combining: Pivots, Joins, Appends & Folders of Files

This is where analysts get dangerous: reshape anything, stitch everything, and let a whole folder of monthly files become one clean table.

🎯 Objectives

Master the essential 20 transformations deeply · unpivot like breathing · all 6 join kinds incl. anti joins · Group By power · combine a folder of monthly files automatically.

📘 2.1 Column-surgery transformations (Transform tab)

Split Column (delimiter/positions/number of chars), Merge Columns, Format (Trim/Clean/Case), Replace Values, Extract (age/first chars), Fill Down (classic for "state-once-then-blank" exports), Replace Errors, Data Type (+locale), Rename. Column from Examples = pattern inferencing — try it on messy data before hand-coding.

📘 2.2 Reshaping rows ↔ columns (the mental flip)

🎯 Rule: tall & narrow = model-friendly; wide = presentation-only. Model thinks in unpivoted data; humans like pivoted views — produce both from one source with two queries (Reference).

📘 2.3 Combining: Append & Merge (all 6 joins)

• Append = stack rows (same shape: 2024 file + 2025 file). "as New" vs in-place — prefer "as New".
• Merge = SQL JOIN on keys. The 6 kinds:

• After merge, Expand the ⇆ column to select fields; rename expanded columns to keep keys documented.
• Fuzzy matching (merge options): joins "laptop pro" ≈ "Laptop Pro14" with similarity threshold — rescue tool, not architecture; clean keys first, fuzzy second.

📘 2.4 Combining a FOLDER of files — the crown jewel

1. Get Data → Folder → browse → Combine & Transform.
2. Power Query builds a Sample File + Transform Sample File (function) + fnInvoke per file + appends results, adding Source.Name.
3. Edit the sample transform (not individual files!) — every file gets the same cleaning.
4. Gotchas: filter out temp files (~$), shredded columns from inconsistent headers (standardize upstream), chaos if a file has extra column (rename/reorder inside sample logic, or custom M with Table.PromoteHeaders after Table.RenameColumns…).
5. New month file lands → Refresh → flows in. This is the recipe for real business processes.

📘 2.5 Query hygiene at scale (_Sources → stg_ → Output)

Bronze/Silver/Gold in miniature: _src_SalesCSV (raw, load disabled) → stg_Sales_Clean (steps, load disabled) → Sales (ready, loads). Reference (chain) instead of Duplicate (fork) so fixes propagate. Group folders keep 20 queries navigable.

🧪 LAB 2 — The monthly-files machine (60 min) · data: datasets/monthly/

You have 24 monthly files Sales-YYYY-MM.csv dropped by a fictional ERP.

1. Get Data → Folder → datasets/monthly → Combine & Transform. Power Query shows sample + combined queries.
2. In Transform Sample File: verify types (OrderDate date, Qty/price numbers, Discount decimal). Keep the steps lean.
3. Back in combined query: remove Source.Name column? Keep it! Add column MonthFile = [Source.Name]… actually extract YYYY-MM: Split by -/. → keep part 1&2 → FileMonth.
4. Sanity: Group By → Region… wait — Region isn't in sales; Group By → FileMonth, Revenue = Sum of Quantity × … — instead: Group by FileMonth with Sum of Quantity + All Rows; inspect aggregated vs detailed; then remove the Group step (we load row-level, aggregation belongs in DAX — remember!).
5. Filter out any row where Source.Name contains ~$ or temp.
6. Close & Load. Drop Sales-2025-99.csv–new file later → Refresh → row count grows. That's the machine. ✅
7. Stretch: Merge combined sales with Products (name cleanup first on the product side if names messy) — validate with a Left Anti join to count orphans.

💪 Exercises

1. Unpivot this: convert Region | Jan-Feb-Mar sales wide table into Month/Value rows (build it via Enter Data first).
2. All 6 joins on Customers × Sales on CustomerID: record row counts for each; explain Anti results in your journal.
3. Group By with two aggregations + All Rows: per Region (from Customers): total qty + order count + detail.
4. Fuzzy-merge experiment: mess up 3 product names in a copy of Products; fuzzy match success rate at 0.6 vs 0.9 thresholds.
5. Pivot Column drill: turn monthly FileMonth rows into 12 month columns of summed Quantity.

✅ Selected answers

• Ex 2 (on our data): Inner = matching orders only; Left outer = all sales + customer info; Left Anti from Customers = customers who never bought (0 in our seed data… or count it yourself!); Full = everything with null gaps.
• Ex 1: Select Region → right-click → Unpivot Other Columns → rename Attribute→Month, Value→Sales.

❓ Quiz

1. Anti join's business use case (name two). 2. Why edit the sample-file transform and not a per-file output? 3. Wide vs tall for modeling? 4. Group By "All Rows" is useful because…?

✅ Answers

1. Orphan detection (orders without customers), "who didn't buy/report/submit" analysis, change-detection between snapshots.
2. The function applies the same cleaning to every file automatically — file-level edits don't propagate.
3. Tall/narrow; wide tables are for display, not relationships/DAX.
4. You can append aggregate columns while keeping row detail in one step (e.g., add Region-total to every row).

✅ Mastery checklist

• All 6 join kinds built + row counts documented
• Folder-combine machine working with my own sample edits
• Unpivot by muscle memory
• stg_/output naming used instinctively

Next: PQ_03_M_Language.md — write the language itself: let/in, functions, lists, records, custom fx, error-handling M.

PQ 03 — The M Language: From Click-Recipes to Code Mastery

Everything the UI does is M code underneath. Learn M and there's no transformation on Earth you can't express — this is the point where you stop being a clicker.

🎯 Objectives

Read & write M confidently · understand let/in, records, lists, tables · write custom functions & parameters · handle errors like an engineer.

📘 3.1 Anatomy of every query

let
    Source = Csv.Document(File.Contents("C:\data\sales.csv"), ...),
    Typed  = Table.TransformColumnTypes(Source, {{"Qty", Int64.Type}}),
    Added  = Table.AddColumn(Typed, "Revenue", each [Qty] * [Price], Currency.Type)
in
    Added

The 5 laws of M:

1. let … in … — each line names a value; in returns the final one.
2. Line names become Applied Steps; quoted identifiers use #"Name With Spaces".
3. each is sugar for (_) => — a function of one row; [Qty] reads that row's column.
4. M is lazy & functional: values immutable, evaluation on demand, no loops (functions recurse/iterate for you).
5. Types matter: type table, type list, type record, Int64.Type, type date, nullable type text.

📘 3.2 The three data structures you manipulate

record = [Name = "Aarav", City = "Delhi"]          -- dotted access: record[Name]
list   = {1, 2, 3}                                  -- ordinal: list{0} = 1, list{2} = 3
table  = #table({"ID","Price"}, {{1,999},{2,499}})  -- rows = list of lists

Key operators: {1..5} range list, [field] field access, =, <>, and, or, not, if … then … else.

📘 3.3 The 20 functions that cover 90% of real work

📝 Build blocks: a date column in M — = Table.AddColumn(Source, "Month No", each Date.Month([Order Date]), Int64.Type) — note the inline type on AddColumn keeps Changed Type steps unnecessary.

📘 3.4 Custom columns & transforms with each (the inner language)

Add Column → Custom Column is 95% each-logic:

each if [Qty] = null then 1 else [Qty]                     -- defaults
each try Number.From([Price]) otherwise 0                  -- error → fallback
each Text.Proper(Text.Trim([Product]))                     -- chain text ops
each if [Region] = "North" then [Revenue] * 1.05 else [Revenue] -- conditional math
eacheach = no — write ifs as above. Date: each Date.Year([JoinDate]) 

And whole-column operations: Transform Columns → = Table.TransformColumns(prior, {{"Product", each Text.Proper(Text.Trim(_)), type text}}).

📘 3.5 Functions — your own fx army

Custom function (right-click Queries pane → New Query → Blank → paste, then rename):

(payload as text) as text =>
let
    Clean = Text.Proper(Text.Trim(payload)),
    FirstWord = List.First(Text.Split(Clean, " "))
in
    FirstWord

Name it fxFirstWord. Call it anywhere: = Table.AddColumn(T, "FirstWord", each fxFirstWord([Product])). Parameters (Manage Parameters) = user-settable values (folder paths, thresholds) used inside queries: File.Contents(pDataFolder & "\Monthly\sales.csv") — re-point 2024→2025 without touching applied steps. Interview gold: incremental refresh's RangeStart/RangeEnd are exactly this trick.

📘 3.6 Error culture

Errors appear as Error cells with details under the hood. Behaviors:

• Ignore: Remove Errors (after logging root cause).
• Rescue: try expr otherwise fallback — surgical, keeps rows.
• Document & audit: add a flag each [InError] = true/false then replace with null — keep a quality summary query.

Bonus debugging: keep a Nulls count step, click Error cell details for message + record snapshot.

🧪 LAB 3 — Code your own Date table (60 min)

In Power BI, PQ creates a date table — but engineers write it in M for max control (fiscal years!):

let
    Start = #date(2023, 1, 1),
    End   = #date(2025, 12, 31),
    Days  = Duration.Days(End - Start) + 1,
    Dates = List.Dates(Start, Days, #duration(1, 0, 0, 0)),
    T0    = Table.FromList(Dates, Splitter.SplitByNothing(), {"Date"}),
    T1    = Table.TransformColumnTypes(T0, {{"Date", type date}}),
    Yr    = Table.AddColumn(T1, "Year", each Date.Year([Date]), Int64.Type),
    Mo    = Table.AddColumn(Yr, "Month Number", each Date.Month([Date]), Int64.Type),
    MName = Table.AddColumn(Mo, "Month Name", each Date.ToText([Date], "MMMM", "en-IN"), type text),
    FY    = Table.AddColumn(MName, "Fiscal Year", each if Date.Month([Date]) >= 4 then "FY" & Text.From(Date.Year([Date])) else "FY" & Text.From(Date.Year([Date])-1), type text)
in
    FY

Paste into a Blank Query → inspect → India's April fiscal year column in 3 lines. Then: make Start/End parameters: replace #date(...) with pDateStart, pDateEnd (Manage Parameters). Debug encounter: deliberately write [NonexistentCol] in one step, read the error message, fix it — the formula bar is your friend.

💪 Exercises

1. Write M adding IsWeekend = true on Sat/Sun (hint: Date.DayOfWeek([Date], Day.Monday) >= 5).
2. Custom column: PriceBand High ≥15000 / Mid ≥3000 / Low — in one if chain with each.
3. Function fxFullName(first,last) combining w/ proper case; apply to Customers' split names.
4. List.Transform({" north ","NORTH"}, each Text.Proper(Text.Trim(_))) in a blank query; read each line aloud.
5. Implement folder-path parameter pMonthlyFolder; re-point Lab 2's Folder.Combine source step to it via Advanced Editor.

✅ Selected answers

• Ex 1: Table.AddColumn(T, "IsWeekend", each Date.DayOfWeek([Date], Day.Monday) >= 5, Logical.Type)
• Ex 5 (pattern): = Folder.Files(pMonthlyFolder) as the first step.

❓ Quiz

1. What does each compile to and when do you need (_)=> explicitly? 2. try/otherwise vs Remove Errors — data-loss tradeoff? 3. Why inline the type in Table.AddColumn? 4. Parameters exist to…?

✅ Answers

1. each = (_) => taking the current row; the explicit form is needed for nested functions/columns comparisons.
2. try/otherwise rescues rows with fallback values; Remove Errors deletes them — choose by whether the row's other data is trustworthy.
3. It types the column at creation → avoids an extra Changed Type step and type flips later.
4. Externalize changeable values (paths, years, thresholds) so refreshing across environments = one parameter edit.

✅ Mastery checklist

• I wrote & explained the M date table without pasting
• A custom function + parameter live in my file
• try/otherwise used deliberately with documented fallback logic
• Formula bar always ON in my editor

Next: PQ_04_Pro_Enterprise.md — folding, performance, dataflows, incremental refresh, real-world architecture.

PQ 04 — Pro & Enterprise Power Query: Folding, Speed, Dataflows & Architecture

Final level: making queries that refresh millions of rows in seconds, design organizations — not just files — and run from CSVs to APIs to lakes.

🎯 Objectives

Understand & exploit query folding · performance engineering patterns · API/Web connections · incremental refresh & dataflows · reference architecture of a real project.

📘 4.1 Query folding — the deepest PQ secret

Folding = Power Query translates your steps into the source's native language (SQL!) and ships work to the server. A filter+select on SQL Server becomes SELECT … WHERE server-side — millions of rows never leave the database.

• Right-click a step → View Native Query (greyed out when folding broke). Folding stops mattering at CSVs/Excel (no server to fold to); it dominates SQL/Fabric sources.
• Fold-friendly: SelectRows/Remove columns/Rename/Type (mostly)/Sort/Group/Keep rows, merges/joins.
• Fold-breakers: Add Index Column, many custom each text gymnastics (Split via Text.*), Table.Buffer (except when intentional), mixing sources (data privacy firewall), using M functions the driver can't express.
• Pro pattern: do foldable steps FIRST, break-folding ones LAST; and prefer cleaning upstream (a SQL view) when steps break folding.

📘 4.2 Performance playbook

1. Load fewest columns/rows as early as possible (select before transforms).
2. Avoid double work: disable load on staging; Reference, don't Duplicate.
3. Table.StopFolding/Table.Buffer knowingly: Buffer locks a snapshot mid-pipeline (kills folding but stops re-evaluation).
4. Keep source indexes healthy for database filtering columns.
5. Most M perf sins = post-type-change text surgery on millions of rows — move to the source (VIEW/T-SQL) instead.

📘 4.3 Web & API connectors

// Weather JSON for Delhi (Open-Meteo, no key):
let
    Resp  = Json.Document(Web.Contents("https://api.open-meteo.com",
            [RelativePath="v1/forecast", Query=[latitude="28.61", longitude="77.20", hourly="temperature_2m"]])),
    Hours = Resp[hourly][time], Tmps = Resp[hourly][temperature_2m],
    Tbl   = Table.FromColumns({Hours, Tmps}, {"Time", "Temp"}),
    Typed = Table.TransformColumnTypes(Tbl, {{"Time", type datetime}, {"Temp", type number}})
in
    Typed

Patterns: JSON navigation via [key]/{index}, Into Table buttons in the UI, API keys in headers (Headers=[Authorization="Bearer …"] — keep OUT of shared files; use parameters/credentials), RelativePath+Query options (avoids "dynamic data source" refresh blocks in the Service).

📘 4.4 Dataflows & cloud Power Query

Dataflow = Power Query running in the Service (or Fabric): centralize cleaning once → many models consume clean tables. Standard/computed entities, incremental refresh, AI insights. Fabric: Dataflows Gen2 write to Lakehouse/Warehouse. Mindset: if more than one .pbix needs the same cleaned table, it should probably be a dataflow.

📘 4.5 Incremental refresh (the big-file blessing)

Partition by date: refresh only the recent window, store the rest.

1. Create RangeStart/RangeEnd parameters (DateTime).
2. Filter your date column: >= RangeStart and < RangeEnd (must fold! check Native Query).
3. Right-click table → Incremental refresh: store 5 years, refresh last 30 days; optional detect data changes via a modified-date column; "only refresh complete periods".
4. Requires Pro+ (Premium for huge partitions). The CSV-family sources don't fold → point at SQL/Fabric for real implementations.

📘 4.6 Reference architecture of a real project

_Sources:   _src_SQL_Sales    _src_Folder_Budget    _src_API_FX     (load: OFF)
Staging:    stg_Sales_Clean   stg_Budget_Combined   stg_FX_Table    (load: OFF)
Business:   Sales  Budget  FX  Customers  Products  _Date (M-built) (load: ON)
Naming:     folder groups, step names in plain English, parameters for every path/env,
            one README query documenting data quality decisions

Plus: colocate folding checks, set privacy levels deliberately, version queries in .pbip (TMDL/pq text) for Git review.

🧪 LAB 4 — Four pro exercises (90 min)

1. Folding demo: connect to any SQL-ish source you have (or simulate via CSV + note where "View Native Query" is absent). List 3 steps that break folding.
2. Delhi weather API: paste §4.3's query; convert hourly time; chart later in a report. Add parameter pLat, pLon.
3. Incremental refresh simulation: build RangeStart/RangeEnd parameters + the filter step on Sales; write the remaining click-path you'd use in a Premium workspace into your journal.
4. Layout your ShopKart master file into _Sources / stg_ / Output groups with disabled loads; add one README query (Enter Data) documenting every cleaning decision made in P2/P3 labs.

💪 Exercises

1. Your refresh takes 11 min on CSV files. Folding won't save you — list three things that actually will (fewer columns, disabled loads on staging, buffering, file format…).
2. Fetch JSON of https://api.github.com/users/octocat (public, no auth) and flatten 3 fields into a table.
3. Walk through a scenario where Table.Buffer both helps and hurts — describe each.
4. Modify §4.3 to pull yesterday only (hint: Query params start/end via Date.ToText(DateTime.Date(DateTime.LocalNow())-#duration(1,0,0,0),"yyyy-MM-dd")).
5. Design a dataflow plan: your org's Sales table needed by 5 reports — entities, workspace, refresh cadence.

✅ Selected answers

• Ex 1: remove unused columns earliest; CSV→Excel-native/source-database migration; Table.Buffer after an expensive step; use Excel workbook named-tables (not sheet regions) as sources; disable load on staging.
• Ex 3: Helps = isolates steps from being recomputed per downstream reference; Hurts = kills folding → whole table flows through the PQ engine → memory + time.

❓ Quiz

1. What does "View Native Query" prove? 2. Why must the incremental-refresh date filter fold? 3. Dataflow vs per-file PQ — organizational tradeoffs? 4. Why RelativePath/Query in Web.Contents?

✅ Answers

1. That everything up to that step folded into a single SQL statement — server does the work.
2. Else every refresh pulls the full history to evaluate the filter — incremental refresh degrades into full refresh.
3. Dataflows = one governed clean layer for many consumers (+central refresh) vs duplicated per-file logic drifting apart; cost = extra workspace management.
4. It keeps the URL static for credential/privacy evaluation → scheduled refresh in the Service isn't blocked as a "dynamic data source".

✅ Mastery checklist

• I can name fold-friendly vs fold-killing steps from memory
• Live API data landed in my model via parameter-relative Web.Contents
• My master file uses _Sources/stg_/Output, disabled loads, parameters, README
• I can whiteboard dataflows + incremental refresh for any company scenario

Track complete. 🎉 Power Query now powers your Power BI (Module 02), Excel (E5), and Fabric ( Module 08 ) — go build something real.

P5 · Power Query for Finance: Trial Balances, Budgets, Fiscal Calendars & Consolidation

Finance data arrives as ugly exports: debit/credit columns, sign chaos, 12 tab files, entity per folder. These recipes turn that mess into governed, refreshable pipelines.

🎯 Objectives

TB/GL cleaning patterns · sign-by-type logic · Actual+Budget merge done right · fiscal-year calendars in M · multi-file/entity consolidation · currency handling.

📘 FQ1. The finance shapes you'll meet

• Trial balance / GL dump: one row per account per period, sometimes Debit/Credit in two columns → normalize to one signed Amount (= [Debit] - [Credit] or type-driven sign).
• Sign chaos: some systems export revenue negative (credit convention). Fix once, centrally, with a conditional column — never in twelve downstream formulas.
• Budget exports: usually per account per month separate files or wide tables (Jan..Dec columns) → unpivot (P2 muscles!).
• Bank/CC statements: text amounts like 1,20,345.67 Cr → Text.Remove/Select + locale typing.

House pattern for signs: keep raw Amount positive + keep Type; compute SignedAmount = if List.Contains({"Revenue","Other Income","Liability","Equity"}, [Type]) then [Amount] else -[Amount] — one step, documented, downstream math becomes natural (Income − Costs).

📘 FQ2. Actual ⟷ Budget merge — the correct join

Merge stg_Finance_GL ⟷ stg_Finance_Budget on MonthStart + AccountCode (multi-column merge: hold Ctrl to pick both) → join kind Full Outer (budget exists without spend and vice-versa — both must survive) → Expand Budget → Variance = [Amount] - [Budget] → Coalesce nulls → rename steps like MERGE Actual+Budget.

📘 FQ3. Fiscal calendar in M (April–March, India)

// Add to your M date table (from P3 lab):
FiscalYear = Table.AddColumn(prior, "Fiscal Year",
    each let m = Date.Month([Date]), y = Date.Year([Date])
         in if m >= 4 then "FY" & Text.From(y) & "-" & Text.End(Text.From(y+1),2)
            else "FY" & Text.From(y-1) & "-" & Text.End(Text.From(y),2), type text),
FiscalMonthNo = Table.AddColumn(FiscalYear, "Fiscal Month",
    each Number.Mod(Date.Month([Date]) + 8, 12) + 1, Int64.Type),   // Apr=1 … Mar=12
FiscalQtr = Table.AddColumn(FiscalMonthNo, "Fiscal Qtr",
    each "FQ" & Text.From(Number.RoundUp([Fiscal Month]/3)), type text)

Parameters pFiscalStartMonth (=4) generalize this for any country's fiscal year — that's the consultant-grade touch.

📘 FQ4. Multi-entity consolidation (folders of GLs)

Structure: consolidation/EntityA_GL.csv, EntityB_GL.csv … → Folder combine (P2 machine!) → add Entity = Text.BetweenDelimiters([Source.Name], "_", ".") → clean once in the sample transform → Entity becomes a column → later a dimension (dim_Entity) enabling entity slicers + RLS-by-entity. This is literally how group finance teams automate month-end packs.

📘 FQ5. Currency: FX rates into transactions

datasets/Finance_FX.csv (monthly avg USDINR/EURINR/GBPINR): type months → Unpivot currency columns → merge into transactions on (MonthStart, Currency) → Amount_INR = [Amount] * [Rate]. For daily rates: sort + Fill Down carries the last rate across weekends (the M equivalent of LOCF).

🧪 LAB P5 — The Finance engine (75 min)

1. Load Finance_GL.csv → stg_Finance_GL: types (MonthStart date, code text, amount currency); add SignedAmount conditional column (FQ1 pattern).
2. Load Finance_Budget.csv → stg_Finance_Budget (type months/amounts).
3. Merge as New fact_Finance_Pack: Full Outer on MonthStart+AccountCode → expand Budget → replace errors/nulls → add Variance, Variance % (guard divide) → polish names.
4. Append FY fields to your P3 date table code (FQ3) with parameter pFiscalStartMonth; relate in the model and sanity-check April rows show "FY24-25"-style labels.
5. FX mini-lab: build stg_FX unpivoted (MonthStart | Currency | Rate) — you'll merge it onto entity exports in the stretch.
6. Refresh choreography: bump one GL row's amount in the CSV → Refresh → watch Variance move. That's an auditable pipeline. ✅
7. Stretch: fabricate EntityB_GL.csv by copying the file, scaling amounts ×0.6 (Table.TransformColumns), and prove entity-split refresh works; document every decision in your README query.

💪 Exercises

1. TB variant: given Debit/Credit two-column input (create via custom columns from GL), normalize to signed Amount.
2. Write the Full-Outer coalesce step that turns null Budget/Amount into 0 without losing columns (M: Table.ReplaceValue(..., null, 0, Replacer.ReplaceValue, {"Budget","Amount"})).
3. Add Half = H1/H2 fiscal to the date table; group revenue Q-o-Q by Fiscal Qtr in a following query and inspect.
4. Journal: why Full Outer (not Left) for Actual⟷Budget — give the 2 real cases.
5. Fill-Down-days pattern: explain (or implement) how monthly FX becomes daily via a date-table merge + Fill Down.

✅ Selected answers

• Ex 4: budgeted cost with no actual spend yet (left-join would drop the row), and actual spend with no budget line (right-join would drop it) — Full Outer keeps both, nulls coalesce to zero variance rows.
• Ex 2 works because ReplaceValue with Replacer.ReplaceValue substitutes nulls in both listed columns in one recorded step.

❓ Quiz

1. Where should sign conventions be fixed and why centrally? 2. The two-column key for Actual⟷Budget merge? 3. What does Fill Down simulate for FX across weekends? 4. Parameterize fiscal start month to achieve…?

✅ Answers

1. In one conditional step in the staged query — downstream formulas stay natural, fixes happen once, audits read it instantly.
2. MonthStart + AccountCode (period × account granularity).
3. LOCF (last observation carried forward): the latest known rate covers non-trading days.
4. One calendar template reusable for any entity/country fiscal year (e.g., 4 for India, 7 for US-style FYJuly) — consultant portability.

✅ Mastery checklist

• SignedAmount pattern implemented + documented
• Full-Outer variance pack refreshes from CSV edits
• Fiscal date table parameterized (FY/FQ/H)
• I can whiteboard entity consolidation for a real finance team

The 🧹 Power Query track is complete (5 modules). You now own the full pipeline: finance data in → governed pipelines → DAX P&L → CFO dashboards. 🏆

S1 · SQL Foundations: SELECT, WHERE, ORDER BY — Read Any Database

SQL is the language of data itself. Power BI pulls from databases, Excel pulls from databases, every backend stores in one. Learn SQL and no dataset is locked to you ever again.

🎯 Objectives

Understand relational tables · write SELECT/WHERE/ORDER BY/LIMIT · filter with dates, text, NULLs correctly · run your first 20 queries on ShopKart.db.

📘 1.1 The mental model

A database = Excel workbook; a table = one sheet with strict column types; a row = record; a column = field. SQL = the instructions you send: "give me these columns, from that table, where these rules hold, sorted like this." The engine does the work; you declare what, not how.

📘 1.2 The skeleton (memorize the classroom order)

SELECT CustomerName, Segment, Region   -- what columns (or * for all)
FROM Customers                          -- from which table
WHERE Region = 'West'                   -- keep which rows
ORDER BY CustomerName ASC               -- sort (ASC default, DESC flips)
LIMIT 10 OFFSET 5;                      -- SQLite/Postgres: first 10 after skipping 5

Logical execution order (interview favorite): FROM → WHERE → GROUP BY → HAVING → SELECT → ORDER BY → LIMIT. That's why you can't use a SELECT alias in the same WHERE.

📘 1.3 Filtering with WHERE — the 90% toolkit

Region = 'West'                         -- equality (text in single quotes)
Quantity >= 3 AND Discount = 0          -- AND / OR / NOT  (parenthesize mixed logic!)
Region IN ('North', 'South')            -- set membership
Quantity BETWEEN 2 AND 5                -- inclusive range
ProductName LIKE 'Laptop%'              -- start-with ( % = any run, _ = one char )
ProductName LIKE '%mouse%'              -- contains
Discount IS NULL                        -- NEVER write = NULL (NULL means "unknown")
COALESCE(Discount, 0)                   -- swap NULL for a value on the fly

NULL rules: comparisons with NULL return unknown, not true/false → those rows vanish. = NULL matches nothing ever. This causes more beginner bugs than everything else combined.

📘 1.4 Derived columns & aliases

SELECT ProductID, Quantity, UnitPrice,
       Quantity * UnitPrice AS LineTotal,      -- computed column, named
       UPPER(Region) AS RegionUpper            -- string functions (UPPER, LOWER, LENGTH, TRIM, SUBSTR)
FROM Sales;
-- dates (SQLite): strftime('%Y', OrderDate), date(OrderDate,'+7 days'), julianday(a)-julianday(b)

Comments: -- line comment or /* block */. End statements with ;.

📘 1.5 DISTINCT & small bites

SELECT DISTINCT Region FROM Customers; — uniques. SELECT COUNT(DISTINCT CustomerID) FROM Sales; — preview of aggregations (S2). LIMIT/TOP/FETCH FIRST differ by dialect: SQLite = LIMIT; SQL Server = TOP n; always check your engine.

🧪 LAB 1 — Interrogate ShopKart (40 min)

Setup (one time): Install DB Browser for SQLite (free) → Open Database → datasets/ShopKart.db → "Execute SQL" tab. (Or use VS Code's SQLite extension / any online SQLite runner by uploading the file.)

Run each, predict first, verify:

SELECT * FROM Products LIMIT 5;
SELECT ProductName, UnitPrice FROM Products WHERE Category = 'Electronics' ORDER BY UnitPrice DESC;
SELECT Region, Segment FROM Customers WHERE City IN ('Delhi','Mumbai');
SELECT OrderID, OrderDate FROM Sales WHERE Quantity >= 7 AND Discount > 0 ORDER BY OrderDate;
SELECT COUNT(*) AS OrderCount FROM Sales;                       -- whole fact table, one number
SELECT ProductName FROM Products WHERE ProductName LIKE '%Laptop%';
SELECT CustomerID, COALESCE(Discount,0) FROM Sales LIMIT 10;
-- date slicing:
SELECT * FROM Sales WHERE OrderDate BETWEEN '2025-01-01' AND '2025-03-31';
SELECT strftime('%Y-%m', OrderDate) AS Month, COUNT(*) FROM Sales GROUP BY Month LIMIT 6;

💪 Exercises

1. All products priced between 5,000 and 15,000, cheapest first.
2. Customers whose names start with 'A' in the South or East.
3. Orders not from West, under quantity 3, with any discount.
4. Every distinct discount value in Sales.
5. The 10 most recent orders of 2024.
6. Write a query that fails because of = NULL, fix it with IS NULL (check Discount).

✅ Selected answers

• Ex 2: WHERE CustomerName LIKE 'A%' AND Region IN ('South','East')
• Ex 5: WHERE OrderDate BETWEEN '2024-01-01' AND '2024-12-31' ORDER BY OrderDate DESC LIMIT 10

❓ Quiz

1. Why does the execution order matter (FROM→WHERE→SELECT→ORDER BY)? 2. LIKE '_ouse' matches what? 3. Why IS NULL, not = NULL? 4. Difference WHERE vs HAVING (preview)?

✅ Answers

1. Aliases made in SELECT aren't visible to WHERE because WHERE runs first; ORDER BY runs after SELECT so aliases work there.
2. One char + "ouse": 'Mouse', 'House' — _ is exactly one character.
3. NULL means "unknown": unknown = anything → unknown → excluded. IS NULL tests existence of unknown-ness.
4. WHERE filters rows before grouping; HAVING filters groups after — S2 unlocks it.

✅ Mastery checklist

• 20 queries run against ShopKart.db with output sanity-checked
• Text/date/NUL L filters from memory
• Execution order recited while writing

Next: SQL_02_Joins_Aggregations.md — where SQL starts feeling like analysis, not lookup.

S2 · Joins & Aggregations: GROUP BY, HAVING & Sticking Tables Together

*One table answers questions. Joins + GROUP BY answer business questions: revenue by region, top customers, category share — the analytics paychecks.*

🎯 Objectives

Master the aggregate five + COUNT variants · GROUP BY / HAVING fluently · join confidently (inner/left/right/full/cross) · combine both in real reporting queries.

📘 2.1 Aggregation: collapse rows into answers

SELECT COUNT(*) AS Orders,            -- row count
       SUM(Quantity) AS Units,        -- totals
       AVG(UnitPrice) AS AvgPrice,
       MIN(OrderDate) AS FirstOrder, MAX(OrderDate) AS LastOrder,
       COUNT(DISTINCT CustomerID) AS Customers
FROM Sales;

SUM/AVG/MIN/MAX etc. ignore NULLs (except COUNT(*) which counts all rows; COUNT(col) skips NULLs — sneaky interview question).

📘 2.2 GROUP BY — buckets first, then aggregate

SELECT Region, COUNT(*) AS Customers
FROM Customers
GROUP BY Region
HAVING COUNT(*) >= 3            -- filter the GROUPS (WHERE filters rows pre-group)
ORDER BY Customers DESC;

The unbreakable law: every SELECTed column must be either (a) in GROUP BY, or (b) inside an aggregate. Order of writing: SELECT … FROM … WHERE … GROUP BY … HAVING … ORDER BY. Grouping keys appear exactly; expressions group logically (GROUP BY strftime('%Y-%m', OrderDate)).

📘 2.3 Joins — merging tables on keys

SELECT s.OrderID, s.OrderDate, p.ProductName, p.Category
FROM Sales s
INNER JOIN Products p ON s.ProductID = p.ProductID;

Aliases s / p keep queries readable. Multi-join chains: Sales → Products → and → Customers in one FROM.

📘 2.4 Anti-patterns via joins

• Orphans (sales with no matching product): LEFT JOIN Products … WHERE p.ProductID IS NULL — the SQL anti-join (same idea as Power Query's Left Anti!).
• Duplicates from one-to-many joins: joining at wrong grains multiplies rows → check row counts before/after; COUNT vs COUNT(DISTINCT …) tells the story.

📘 2.5 Combined pattern you will type forever

SELECT c.Region,
       p.Category,
       SUM(s.Quantity * s.UnitPrice * (1 - s.Discount)) AS Revenue
FROM Sales s
JOIN Customers c ON s.CustomerID = c.CustomerID
JOIN Products  p ON s.ProductID  = p.ProductID
WHERE s.OrderDate >= '2025-01-01'
GROUP BY c.Region, p.Category
HAVING SUM(s.Quantity) > 10
ORDER BY Revenue DESC;

🧪 LAB 2 — ShopKart business answers (50 min)

1. Units & revenue (with discount math) by Region for 2025, best first.
2. Revenue by Category × YearMonth (group by strftime('%Y-%m', OrderDate)), limited to Electronics+Appliances.
3. Average order value per Segment: SUM(rev)/COUNT(DISTINCT OrderID) grouped by Segment.
4. Top 5 customers by lifetime revenue with their names and cities (join, group, order, limit).
5. Orphan audit: any Sales rows without Customer or Product matches? (Two anti-join queries; both should return 0 — why is 0 the right answer?)
6. Targets vs actuals quickie: total SalesTarget by Region (Targets table) vs 2025 achieved revenue per region (two different grains — journal the care needed; S5 and Power BI M10 own this properly).

💪 Exercises

1. Categories sorted by average discount % given (AVG of Discount).
2. Months where revenue dropped vs the previous month — using only GROUP BY + HAVING tricks (hint: not trivial without window functions; attend S3 for the clean solution).
3. Customers count per City where count > 1, cities alphabetically.
4. Revenue share: category revenue / grand total inline (subquery SELECT SUM(...) without group) — write it two ways.
5. Join chain sanity: Sales join Products vs Sales join Products join Customers — verify row counts stay 1300 in both (why does Customer join not change the count?).

✅ Selected answers

• Ex 3: SELECT City, COUNT(*) FROM Customers GROUP BY City HAVING COUNT(*)>1 ORDER BY City;
• Ex 5: one-to-many join with unique keys on the "one" side never multiplies rows — that's why PK/UK matters.

❓ Quiz

1. WHERE vs HAVING in one line each. 2. Why can a LEFT JOIN show NULLs on the right side? 3. The law of SELECT inside GROUP BY queries? 4. COUNT(*) vs COUNT(col)?

✅ Answers

1. WHERE filters rows before grouping; HAVING filters groups after aggregation.
2. No match found → left row survives with right columns padded NULL — that's literally the point of LEFT joins (find matches if any).
3. Columns must be group keys or aggregated — otherwise the engine can't know which row's value to show.
4. COUNT(*) counts all rows incl. NULL-col rows; COUNT(col) skips rows where col is NULL.

✅ Mastery checklist

• Group+aggregate queries written without syntax trips
• Two-table and three-table joins chained comfortably
• Row-count checks habitual before reporting any join result
• Anti-join audit query memorized

Next: SQL_03_Intermediate.md — subqueries, CTEs, window functions (the promotion to "writes clever SQL").

S3 · Intermediate SQL: Subqueries, CTEs & Window Functions

This module is the pivot from "can query" to "writes SQL others steal". Windows especially — they power running totals, period-over-period, top-N-per-group.

🎯 Objectives

CASE WHEN logic columns · subqueries (scalar/IN/EXISTS/correlated) · clean, readable CTE chains · window functions end-to-end · set operations.

📘 3.1 CASE — the if/else of SQL

SELECT ProductName,
       CASE WHEN UnitPrice >= 15000 THEN 'High'
            WHEN UnitPrice >= 3000  THEN 'Mid'
            ELSE 'Low' END AS PriceBand,
       CASE Category WHEN 'Electronics' THEN 1 ELSE 0 END AS IsElec
FROM Products;

Two forms: searched (CASE WHEN cond THEN…) / simple (CASE col WHEN val THEN…). Works inside aggregates: SUM(CASE WHEN Region='West' THEN rev ELSE 0 END) = conditional aggregation — the SQL twin of CALCULATE.

📘 3.2 Subqueries — a query inside a query

-- scalar: one cell
SELECT * FROM Sales WHERE UnitPrice > (SELECT AVG(UnitPrice) FROM Sales);
-- IN: membership against a derived set
SELECT * FROM Products WHERE ProductID IN
  (SELECT ProductID FROM Sales GROUP BY ProductID HAVING SUM(Quantity) > 100);
-- EXISTS: semi-join existence test (fast, no duplicates)
SELECT c.* FROM Customers c
WHERE EXISTS (SELECT 1 FROM Sales s WHERE s.CustomerID = c.CustomerID);
-- correlated: inner references outer row-by-row (elegant, often slower)
SELECT s.* FROM Sales s
WHERE s.Quantity > (SELECT AVG(x.Quantity) FROM Sales x WHERE x.ProductID = s.ProductID);

NOT IN/NOT EXISTS carry a NULL grenade (NOT IN with a NULL in the set returns nothing ever — prefer NOT EXISTS).

📘 3.3 CTEs — readable pipelines (WITH)

WITH Rev AS (
  SELECT strftime('%Y-%m', OrderDate) AS Month,
         SUM(Quantity * UnitPrice * (1-Discount)) AS Revenue
  FROM Sales GROUP BY Month
),
BigMonths AS (SELECT * FROM Rev WHERE Revenue > 500000)
SELECT * FROM BigMonths ORDER BY Month;

Same power as nested subqueries, 10× readability — stages named like Applied Steps. Recursive CTEs (WITH RECURSIVE) generate series/dates — research topic, one-line mention.

📘 3.4 Window functions — rows keep their identity while aggregating

OVER (PARTITION BY group ORDER BY time ROWS/RANGE …)

SELECT OrderDate, Revenue,
  SUM(Revenue)  OVER (ORDER BY OrderDate)                 AS RunningTotal,
  AVG(Revenue)  OVER (ORDER BY OrderDate ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) AS MovAvg3,
  LAG(Revenue, 1) OVER (ORDER BY OrderDate)               AS PrevMonth,
  FIRST_VALUE(Revenue) OVER (ORDER BY OrderDate)          AS FirstValue
FROM MonthlyRev;
SELECT Region, ProductName, Revenue,
  ROW_NUMBER() OVER (PARTITION BY Region ORDER BY Revenue DESC) AS RN,
  RANK()       OVER (PARTITION BY Region ORDER BY Revenue DESC) AS Rk,
  NTILE(4)     OVER (ORDER BY Revenue DESC)               AS Quartile
FROM RegionProductRev;

• ROW_NUMBER unique 1..n · RANK shares ties & gaps · DENSE_RANK ties without gaps.
• Growth math: (Revenue - LAG(Revenue) OVER w) / LAG(Revenue) OVER w = YoY/MoM in pure SQL.
• Top-N-per-group = wrap ROW_NUMBER in CTE and filter WHERE RN <= 3 (The Pattern).

📘 3.5 Set operations

UNION (stack + dedupe), UNION ALL (stack, keep dups — faster, usually right), INTERSECT, EXCEPT. Column-count/types must match; names come from the first SELECT.

🧪 LAB 3 — Analytics-grade ShopKart queries (60 min)

1. Monthly revenue CTE → RunningTotal and MovAvg3 windows over it; sanity-check the first two months' avg manually.
2. PrevMonth via LAG → MoM% = (cur - prev)/prev, blank-safe with NULLIF(prev,0).
3. YoY: LAG with offset 12 on the monthly CTE (months contiguous — confirm!) → YoY %.
4. Top-3 products per Region by revenue: build region×product revenue CTE → ROW_NUMBER partition → filter.
5. PriceBand CASE on Products → customer count per band per Region (conditional aggregation with SUM(CASE WHEN)).
6. EXISTS audit: customers who never bought (NOT EXISTS) — count and list (expected 0 or small; note what it means in RL).

💪 Exercises

1. Months above the average month revenue (scalar subquery approach) vs CTE approach — feel the readability.
2. DENSE_RANK categories by units regionally; find ties.
3. Cumulative % of total revenue per product sorted desc (window sums) — a Pareto column.
4. INTERSECT: customers buying both Electronics AND Appliances (two IN subqueries intersected) vs EXISTS — try both.
5. Detect the NOT IN NULL trap deliberately with a tiny demo table in a temp query.

✅ Selected answers

• Pareto: SUM(Revenue) OVER (ORDER BY Revenue DESC) / SUM(Revenue) OVER () → cumulative share.
• Elect+Appl: SELECT CustomerID FROM Sales WHERE CustomerID IN (…,p.Category='Electronics') INTERSECT SELECT … 'Appliances'.

❓ Quiz

1. CTE vs nested subquery — same power, what changes? 2. ROW_NUMBER vs RANK vs DENSE_RANK ties? 3. Why NOT EXISTS > NOT IN? 4. What does OVER () alone mean?

✅ Answers

1. Readability & reuse: named stages you can reference multiple times; optimizer treats them alike.
2. ROW_NUMBER never ties (arbitrary order within ties), RANK ties share a number and skips the next, DENSE_RANK ties share and doesn't skip.
3. A single NULL in the NOT IN list negates everything (three-valued logic); NOT EXISTS is NULL-safe.
4. Window over all rows — grand aggregates without collapsing detail (e.g., % of total inline).

✅ Mastery checklist

• LAG-based YoY/MoM written unaided
• Top-N-per-group pattern on demand
• Conditional aggregation inside CASE+SUM
• Queries organized as CTE chains by habit

Next: SQL_04_Advanced.md — views, indexes, plans, transactions, optimization — the DBA-worthy layer.

S4 · Advanced SQL: Views, Indexes, Plans, Transactions & Optimization

The senior layer: make data reusable (views), make it fast (indexes), make it trustworthy (transactions), and read the optimizer's mind (plans).

🎯 Objectives

Create views & materialized thinking · understand B-tree indexes & when they fail · EXPLAIN query plans · transactions & integrity · the optimization playbook · enterprise objects overview.

📘 4.1 Views — saved queries as virtual tables

CREATE VIEW vw_MonthlyRevenue AS
SELECT strftime('%Y-%m', s.OrderDate) AS Month,
       SUM(s.Quantity * s.UnitPrice * (1 - s.Discount)) AS Revenue
FROM Sales s GROUP BY Month;
-- now anyone: SELECT * FROM vw_MonthlyRevenue WHERE Month >= '2025-01';

Views = logic reuse + security boundary (expose columns, hide the table). Materialized views (Postgres/Oracle/SQL Server indexed views) store pre-computed results — the database world' aggregations (hi, Power BI!).

📘 4.2 Indexes — the B-tree superpower (and its kryptonite)

CREATE INDEX idx_sales_date ON Sales (OrderDate);
CREATE INDEX idx_sales_cust ON Sales (CustomerID, OrderDate);   -- composite: left-prefix rule

• Index = sorted structure: WHERE/JOIN on indexed column = phone-book lookup instead of full scan (1000× on big tables).
• Left-prefix rule: (CustomerID, OrderDate) serves CustomerID and (CustomerID+OrderDate) filters, not OrderDate alone.
• Kryptonite: functions on indexed columns (WHERE strftime('%Y', OrderDate)='2025' defeats the index → SARGable rewrite: WHERE OrderDate >= '2025-01-01' AND OrderDate < '2026-01-01'), wildcards like '%abc' (leading %) , small tables (scan is cheaper), write-heavy tables (indexes cost on INSERT/UPDATE).

📘 4.3 EXPLAIN — x-ray the optimizer

EXPLAIN QUERY PLAN
SELECT c.Region, SUM(s.Quantity) FROM Sales s JOIN Customers c USING(CustomerID)
WHERE s.OrderDate >= '2025-01-01' GROUP BY c.Region;

Look for: SCAN (full table read) vs SEARCH ... USING INDEX, join order (small first), "temporary b-tree" (extra sort work). Read plans before blaming hardware.

📘 4.4 Transactions — all or nothing (ACID)

BEGIN;
UPDATE Products SET UnitPrice = UnitPrice * 1.05 WHERE Category = 'Electronics';
DELETE FROM Sales WHERE OrderDate < '2020-01-01';
COMMIT;                                  -- or ROLLBACK;

Atomicity (all-or-nothing), Consistency (rules hold), Isolation (other readers see pre-commit state), Durability (committed = permanent). Wrap multi-writes; test on a copy first. Isolation levels (READ COMMITTED, SERIALIZABLE) matter at scale — enterprise databases vary.

📘 4.5 Integrity & shape objects

• Constraints: PRIMARY KEY, FOREIGN KEY … REFERENCES, UNIQUE, NOT NULL, CHECK (Price > 0).
• Triggers: auto-run on INSERT/UPDATE/DELETE (audit logs, totals maintenance) — powerful, debugging-hostile; use sparingly.
• Stored procedures & functions (SQL Server/Postgres): parameterized server-side logic — sqlite lacks them; dialect-aware enterprise topic.
• Temp tables / table variables for staging within a session.

📘 4.6 The optimization playbook (interview-ready list)

1. SARGable predicates (no functions on filtered columns).
2. Select exactly what you need — SELECT * forces extra I/O and kills covering-index chances.
3. Index the join + filter columns; composites follow the left-prefix rule; drop unused indexes.
4. EXISTS for semi-joins beats IN on big sets; avoid correlated subqueries on large tables (rewrite as joins/windows).
5. Aggregate early, join late — shrink rows before the expensive operation.
6. Keep stats updated (ANALYZE); partition giant tables by date; measure with plans, not vibes.

🧪 LAB #S4 — Production-grade ShopKart (60 min)

1. Create vw_MonthlyRevenue (above) + vw_CustomerLTV (per-customer totals) → query both with filters.
2. Plan hunt: EXPLAIN WHERE strftime('%Y', OrderDate)='2025' → note SCAN. Add idx_sales_date; rewrite SARGably (BETWEEN '2025-01-01' AND '2025-12-31'); EXPLAIN again → watch the plan change.
3. Composite index experiment: create (CustomerID, OrderDate); EXPLAIN a CustomerID-only query vs an OrderDate-only query — record the left-prefix proof.
4. Transaction drill: BEGIN → raise prices 5% on Furniture → SELECT check → ROLLBACK → SELECT again (history untouched). Repeat with COMMIT.
5. Integrity: try INSERT INTO Sales (CustomerID…) VALUES (999…) → FK blocks it (if enforced via PRAGMA foreign_keys=ON) — document why referential integrity is sacred. Drop your indexes at the end for downstream labs (DROP INDEX), journal why indexes are a tradeoff, not a checkbox.

💪 Exercises

1. Design the index(es) for "all 2025 orders of a given product" — two columns, right order; defend.
2. A colleague's query is slow: SELECT * FROM Sales WHERE UPPER(PaymentType)='UPI' — diagnose & rewrite both query + (better) data fix.
3. Rewrite a correlated subquery as a window-function version — compare plan text.
4. Create a view that hides raw UnitPrice and exposes price bands + revenue — a security pattern.
5. Journal: three scenarios where you would avoid adding an index.

✅ Selected answers

• Ex 1: (ProductID, OrderDate) or (OrderDate, ProductID)? Filter equality on ProductID + range on OrderDate → equality first, range second: (ProductID, OrderDate).
• Ex 2: SARGable kill: precompute/store normalized value or WHERE PaymentType IN ('UPI','upi') case-insensitively only if collation allows; best: fix data to a canonical case.

❓ Quiz

1. Left-prefix rule in one sentence? 2. What does a full SCAN in a plan mean for table size vs selectivity? 3. Why COMMIT/ROLLBACK both exist in transactions? 4. Materialized vs normal view?

✅ Answers

1. A composite index (a,b) serves queries filtering on a alone AND (a,b) — but not on b alone.
2. Engine reads the entire table — fine for small/high-selectivity queries, a red flag for big tables when you expected an index seek.
3. COMMIT makes the whole unit permanent; ROLLBACK undoes the whole unit — atomicity's two outcomes.
4. Normal view = stored SQL, computed at query time; materialized = stored results (fast reads, needs refresh) — enterprise aggregation pattern.

✅ Mastery checklist

• Indexes created justifiably + plans read aloud
• Transaction test (commit vs rollback) done twice
• Optimization rules applied to three real queries
• Security-view pattern understood

Next: SQL_05_Finance.md — the finance finale: trial balances, variance, fiscal logic, reconciliation in pure SQL.

S5 · SQL for Finance: GL Reporting, Variance, Fiscal Logic & Reconciliation

The payoff module: write the queries a finance system runs on — P&L from a GL, Actual-vs-Budget packs, fiscal calendars, running balances, and month-end reconciliation.

🎯 Objectives

Sign-normalize finance data in SQL · aggregate GL into P&L lines · full-outer variance · fiscal year logic · semi-additive balances with windows · reconciliation (the month-end closer skill).

📘 5.1 Sign convention once, centrally (doctor's orders: in a view)

Finance exports typically hold positive amounts + a Type classifier. Normalize in a view so every downstream query writes natural math:

CREATE VIEW vw_GL AS
SELECT MonthStart, AccountCode, AccountName, Statement, Type, Amount,
  CASE WHEN Type IN ('Revenue','Other Income','Liability','Equity') THEN Amount
       ELSE -Amount END AS SignedAmount
FROM FinanceGL;

📘 5.2 P&L aggregation — CASE-conditional grouping

SELECT MonthStart,
  SUM(CASE WHEN Type IN ('Revenue','Other Income') THEN Amount ELSE 0 END) AS TotalIncome,
  SUM(CASE WHEN Type = 'COGS'  THEN Amount ELSE 0 END)                        AS COGS,
  SUM(CASE WHEN Type = 'OpEx'  THEN Amount ELSE 0 END)                        AS OpEx
FROM FinanceGL
WHERE Statement = 'P&L'
GROUP BY MonthStart
ORDER BY MonthStart;
-- derived: Gross = TotalIncome - COGS; Operating = Gross - OpEx; Margin% = Operating/TotalIncome

📘 5.3 Actual ⟷ Budget variance (FULL OUTER lives here)

SQLite-template (Postgres/SQL Server use FULL JOIN natively):

WITH A AS (
  SELECT MonthStart, AccountCode, MAX(AccountName) AccountName, SUM(Amount) Actual
  FROM FinanceGL WHERE Statement='P&L' GROUP BY MonthStart, AccountCode
),
B AS (
  SELECT MonthStart, AccountCode, MAX(AccountName) AccountName, SUM(Budget) Budget
  FROM FinanceBudget GROUP BY MonthStart, AccountCode
),
U AS (
  SELECT COALESCE(a.MonthStart,b.MonthStart) MonthStart,
         COALESCE(a.AccountCode,b.AccountCode) AccountCode,
         COALESCE(a.AccountName,b.AccountName) AccountName,
         COALESCE(a.Actual,0) Actual, COALESCE(b.Budget,0) Budget
  FROM A a LEFT JOIN B b ON a.MonthStart=b.MonthStart AND a.AccountCode=b.AccountCode
  UNION
  SELECT COALESCE(a.MonthStart,b.MonthStart), COALESCE(a.AccountCode,b.AccountCode),
         COALESCE(a.AccountName,b.AccountName), COALESCE(a.Actual,0), COALESCE(b.Budget,0)
  FROM B b LEFT JOIN A a ON a.MonthStart=b.MonthStart AND a.AccountCode=b.AccountCode
  WHERE a.AccountCode IS NULL
)
SELECT *, Actual - Budget AS Variance,
       ROUND(100.0 * (Actual - Budget) / NULLIF(Budget,0), 1) AS VariancePct
FROM U ORDER BY MonthStart, ABS(Variance) DESC;

Favourability flips by Type (cost vs revenue): add a CASE for a Fav/Unfav column — sign logic with the numbers, not in your head.

📘 5.4 Fiscal years in SQL (April–March)

SELECT *,
  CASE WHEN CAST(strftime('%m', MonthStart) AS INT) >= 4
       THEN 'FY' || strftime('%Y', MonthStart) || '-'
                 || substr(strftime('%Y', date(MonthStart,'+1 year')),3,2)
       ELSE 'FY' || strftime('%Y', date(MonthStart,'-1 year')) || '-'
                 || substr(strftime('%Y', MonthStart),3,2)
  END AS FiscalYear
FROM FinanceGL;
-- then group BY FiscalYear, FiscalQtr (same CASE pattern on month → quarter)

📘 5.5 Running position & semi-additive balances

Stocks use last observation in period (same rule as DAX LASTDATE):

WITH bs AS (SELECT * FROM FinanceGL WHERE Statement='B/S')
SELECT MonthStart, AccountName, Amount,
  SUM(Amount) OVER (PARTITION BY AccountName ORDER BY MonthStart) AS Cumulative_buildup,  -- flows only! educationnal
  FIRST_VALUE(Amount) OVER (PARTITION BY AccountName ORDER BY MonthStart DESC) AS PeriodEndValue
FROM bs;
-- Max month per account (classic anti-window pattern):
SELECT b.* FROM bs b JOIN (SELECT AccountName, MAX(MonthStart) m FROM bs GROUP BY AccountName) t
  ON b.AccountName=t.AccountName AND b.MonthStart=t.m;
-- Running bank balance from transactions:
SELECT txnDate, Amount, SUM(Amount) OVER (ORDER BY txnDate, txnId) AS RunningBalance FROM BankTxns;

📘 5.6 Reconciliation — the month-end closer ritual

• GL vs Bank: entries in GL not in statement (LEFT joins anti-pattern) + vice-versa.
• Duplicate detector: GROUP BY MonthStart, AccountCode HAVING COUNT(*)>1.
• Trial-balance tie-out: SUM(debits) = SUM(credits) — a one-line CHECK query.
• Cut-off check: transactions after period-end but dated inside → WHERE PostingDate > PeriodEnd AND TxnDate <= PeriodEnd.

🧪 LAB S5 — The Finance Pack in SQL (75 min, tables live in ShopKart.db: FinanceGL, FinanceBudget, FX)

1. Create vw_GL (signed) → monthly P&L aggregate → compute Gross/Operating/Margin columns on top via outer SELECT over that CTE.
2. Full variance pack (§5.3) for 2025: add a Fav/Unfav CASE per Type; export mentally earlier vs Power BI M10 version — journal 3 pros/cons SQL-vs-DAX for finance packs.
3. Fiscal columns (§5.4) + FiscalQtr; produce FY totals by Type sorted FY → Type desc.
4. Balances: for Cash, produce PeriodEndValue per quarter (window DESC over quarter boundary) — verify April end ≠ sum of 3 months.
5. FX: monthly USDINR via join on MonthStart → convert a fabricated 2025 USD expense table (create temp) to INR.
6. Recon drills: duplicates check on FinanceGL; tie-out SUM(CASE WHEN Type IN ('Revenue','Other Income')…) vs costs side totals as the TB analog; write 3 month-end checklist queries of your own.

💪 Exercises

1. Top 5 variance-risks: accounts with avg |Variance%| > 8% in 2025.
2. Contra entries pattern: accounts whose sign flipped between two consecutive months (LAG sign check).
3. 12-month rolling OpEx per month using windows on the P&L CTE.
4. Reconciliation count table: GL rows vs Bank rows per month with the anti-counts side-by-side.
5. Journal: why is the TB tie-out the cheapest fraud/error detector in accounting?

✅ Selected answers

• Ex 2: CASE WHEN SIGN(cur)=SIGN(prev)… via LAG(SignedAmount) — flag flips; they're the classic cut-off/reclass red flags.
• Ex 3: AVG(OpEx) OVER (ORDER BY MonthStart ROWS BETWEEN 11 PRECEDING AND CURRENT ROW) on monthly OpEx.

❓ Quiz

1. Why sign-normalize in a view instead of every query? 2. Balances need LAST-observation: state the window pattern. 3. FULL OUTER variance: the two cases it saves? 4. Give 2 reconciliation SQL checks.

✅ Answers

1. Centralized convention → fixes propagate, all consumers share identical numbers, audits read one place.
2. FIRST_VALUE(Amount) OVER (PARTITION BY Account ORDER BY MonthStart DESC) (or latest-month join) within the period.
3. Budgeted-but-unspent lines and spent-but-unbudgeted lines — both must surface as variance, not vanish.
4. Duplicate periods per account; debit/credit tie-out; GL-vs-bank anti-counts; cut-off dated-after-close entries.

✅ Mastery checklist

• P&L & variance & fiscal packs written from scratch on ShopKart.db
• Balance queries respect last-observation semantics
• Reconciliation toolkit documented for a month-end close
• One finance pack compared SQL vs DAX with honest verdicts

SQL track complete. 🏆 You now command the full data stack: Excel → Power Query → SQL → Power BI/DAX — finance-flavored end to end.

T1 · Tableau Foundations: From Zero to Your First Dashboard

If Power BI feels like an office suite, Tableau feels like an art studio for data. Same ShopKart story, new brush. By the end of this module you'll have connected the data, understood blue-vs-green (the ONE idea that makes Tableau click), and built three views.

🎯 Objectives

Install Tableau Public (free) · connect the ShopKart CSVs · master Dimensions vs Measures, Discrete (blue) vs Continuous (green) · build bar + line + detail views · understand Relationships (the "noodle").

📘 1.1 The Tableau family — what to install

👉 We use Tableau Public. Download from public.tableau.com, install, create a free account. Mac AND Windows both work (unlike Power BI!). Web authoring in a browser also works for quick edits.

📘 1.2 Connect to ShopKart

1. Open Tableau Public → left pane "Connect → To a File → Text file" → pick datasets/Sales.csv.
2. You're on the Data Source page: top = connection canvas, bottom = data grid preview.
3. Drag Customers.csv and Products.csv next to Sales. Tableau draws relationships (the "noodle") automatically:
• Sales.CustomerID → Customers.CustomerID
• Sales.ProductID → Products.ProductID
4. Fix data types in the grid header: click the icon on a column → set OrderDate and JoinDate to Date, numeric fields to Number (whole/decimal).

💡 Relationships ≠ Joins. Modern Tableau relates logical tables with a noodle and only fetches what each viz needs — no duplicate-row explosions. You only need physical joins for special cases (we do one in T6).

📘 1.3 The mind-model: Dimensions slice, Measures sum

• Dimensions (blue header): how you cut the data — Category, Region, City, dates.
• Measures (green header): numbers that aggregate — Quantity, UnitPrice, Discount.
• Tableau auto-assigns roles by data type; drag a field between the two halves to convert (e.g., CustomerID counts are better as a Dimension + CNTD).

📘 1.4 Blue vs Green pills — the ONE idea that makes Tableau click

Examples to burn in: MONTH(OrderDate) blue → 12 labeled columns Jan…Dec. MONTH(OrderDate) green → a continuous timeline axis. SUM(Quantity) always green. Right-click any pill to flip discrete↔continuous, or change a date's part↔value.

📘 1.5 Your first three views (build these NOW)

1. Sales by Category: drag Category to Columns, SUM(Quantity) to Rows → vertical bars. Hit the Sort toolbar button to rank; drop Category on Color for polish.
2. Monthly revenue trend: make calc first (right-click in Data pane → Create Calculated Field):

// Revenue — sales price net of discount
[Quantity] * [UnitPrice] * (1 - [Discount])

Then OrderDate (green, continuous MONTH) to Columns, SUM(Revenue) to Rows → a line. Right-click the date axis → Exact Date / Month as needed.

1. Detail drill: Region to Rows, Category to Rows (hello, hierarchy-free drill!), SUM(Revenue) to Text → quick crosstab. Swap axes with the ⇄ button.

Marks card tour: Color, Size, Label/Text, Detail, Tooltip — every drag to these channels literally re-paints the chart. Show Me (top-right) suggests 24 chart types from your selection.

📘 1.6 Filters & formatting basics

• Drag OrderDate → Filters → Years → right-click the filter → Show Filter = interactive sidebar.
• Drag Region to Filters, multi-select → this is how "slicers" work here.
• Format menu → choose clean fonts, drop borders, keep grid lines light (full design theory in T5).

🧪 LAB T1 — First contact with ShopKart (45 min)

1. Connect Sales + Customers + Products with relationships; set correct data types. Screenshot the noodle.
2. Bar: Revenue by SubCategory, sorted desc, bars colored by Category.
3. Line: monthly Revenue (continuous date), add SUM(Quantity) as a second row axis (two charts stacked — dual-axis comes in T2).
4. Crosstab: Region × Segment showing SUM(Revenue); format currency to ₹ with the default properties (right-click Revenue → Default Properties → Number Format → Currency Custom → ₹).
5. Filter: show interactive quick filters for Region and Category; verify numbers change.
6. Save → File → Save to Tableau Public (title: "ShopKart T1"). Open your online gallery — you're published! 🎉

💪 Exercises

1. Make average unit price by Category — notice Tableau default-aggregates everything; try changing SUM→AVG from the pill's drop-down.
2. Blue MONTH vs green MONTH: build both versions of the trend and write 2 sentences on when each wins.
3. Convert CustomerID to a dimension, then build a view showing CNTD(CustomerID) by Region — why not put CustomerID on Text directly?
4. Use Show Me to turn the Category view into a treemap in 3 clicks.
5. Find where Tableau stores marks: drag ProductName onto Detail of a Revenue view — describe what happens to tooltips.

✅ Selected answers

• Ex 1: Right-click the pill → Measure (Sum) → Average. Blue pills slice; green pills always carry an aggregation (SUM, AVG, MIN …).
• Ex 3: Placing IDs on Text lists every customer as a mark — unreadable. COUNTD gives the count of unique customers per slice.
• Ex 5: Detail adds a mark per product → the tooltip now lists each product's revenue; the chart no longer has one bar per region.

❓ Quiz

1. Blue pill vs green pill — what's the core difference?
2. Relationships (noodle) vs physical joins — why is the noodle safer for multi-table data?
3. Dimensions vs Measures — how does Tableau decide, and can you override?
4. Where do you set a column's data type after connecting a CSV?

✅ Answers

1. Blue = discrete labels → headers; green = continuous values → an axis. Dates can be either; measures are (almost) always green.
2. Noodle = logical relationships resolved per-viz, preventing duplicate-row multiplication; physical joins materialize one fixed table (needed for some row-level cross-table calcs).
3. Roles come from data type (text/dates→dimensions, numbers→measures); drag the field across to convert or right-click → Convert to Dimension/Measure.
4. On the Data Source page, click the data-type icon in the column header (also possible later via the field's drop-down in the Data pane).

✅ Mastery checklist

• Connected 3 CSVs with relationships; data types corrected
• Can explain blue vs green without notes
• Built bar + line + crosstab and published to Tableau Public
• Created your first calculated field (Revenue)

Next: T2 — every chart you'll actually use, and the Analytics pane magic (trend lines, forecasts, reference lines). 🚀

T2 · Charts & Visual Analytics: The 10 Views You'll Use Every Week

90% of real Tableau work is bars, lines, maps, dual-axis combos and one glorious scatter — built fast and formatted clean. This module is chart muscle memory on ShopKart.

🎯 Objectives

Master bar/line/area/treemap/scatter/map/dual-axis · hierarchies & drill-down · groups, sets, Top-N filters · the Analytics pane (trend lines, forecasts, reference lines, totals).

📘 2.1 The workhorse: sorted, labeled bars

Revenue by Category, sorted desc (toolbar sort button or pill → Sort → By field). Polish: right-click axis → format ₹; drag SUM(Revenue) to Label; Category to Color. Percent-of-total bars: right-click pill → Quick Table Calculation → Percent of Total (full table-calc theory in T3 — steal it now, master it next).

📘 2.2 Lines, drill-down date hierarchies

OrderDate (green continuous) + SUM(Revenue). The + / − buttons on the date pill expand Year→Quarter→Month→Day live — Tableau's built-in drill. Toggle several members in the legend via Highlight. Swap the green pill to Revenue, add Region to Color → small-multiple vibes with one pill.

📘 2.3 Dual-axis combo — the BI classic

1. Rows: SUM(Revenue), then CNTD(OrderID) (order volume).
2. Right-click the second pill → Dual Axis. Two charts overlay.
3. Right-click the right axis → Synchronize Axis ONLY when units match (₹ vs orders = do NOT sync — instead set independent ranges honestly).
4. Change the second marks card to Bar → line-over-bars combo. Control layer order by dragging pills in Rows.

📘 2.4 Maps of India

1. Check geographic roles: City → Geographic Role → City; State → State/Province; Country — create a calc // Country → 'India' if missing, set its role.
2. Double-click State → Tableau draws a symbol map. Drag SUM(Revenue) to Size, Region to Color.
3. Switch Marks to Map (filled) for a choropleth by state. Edit locations via Map → Edit Locations if any city geocodes wrong (rename to match Tableau's India geography).

⚠️ Tableau Public online render of maps needs internet (tiles); the shape data still works offline once cached.

📘 2.5 Scatter & the Analytics pane