When raw data is messy or comes from multiple systems, Power Query is the right first stop. Power Query connects to a CSV, SQL database, or API, applies a sequence of transform steps (column renames, type changes, merges, unpivots), and refreshes the result on demand — replacing manual copy-paste cleanup. Power Pivot complements it on the modeling side: Power Pivot builds a relational star schema and lets the analyst write DAX measures against it. The role split is "Power Query shapes and loads; Power Pivot models and measures." Built-in text companions are also part of the shaping toolkit: `=CONCAT(range)` joins cells with no separator, while `=TEXTJOIN(",", TRUE, range)` lets the analyst pick the separator and skip empties; a conditional variant spills only matching values: `=TEXTJOIN(", ", TRUE, IF(A2:A100="Active", B2:B100, ""))`. Modern Excel adds `=TEXTSPLIT(text, delim)` for the inverse operation, and `=TEXT(A1, "yyyy-mm-dd")` for embedding formatted dates inside a TEXTJOIN or filename.
Text cleanup leans on a small set of dedicated functions: `=TRIM` removes extra spaces, `=CLEAN` strips non-printable characters, `=SUBSTITUTE(A1, "$", "")` replaces specific characters, and `=NUMBERVALUE("$1,234.56")` parses locale-formatted currency strings to numbers. Substring extraction uses `=LEFT`, `=MID`, and `=RIGHT`, often combined with `=FIND` to locate a delimiter and `=LEN` for total length. Text dates become real dates via `=DATEVALUE("2025-12-31")` or, for fixed-width strings, the manual assembly `=DATE(RIGHT(A1,4), MID(A1,6,2), LEFT(A1,2))`; once arithmetically manipulated, a serial number can be returned to a display string by wrapping it in `=TEXT(value, "yyyy-mm-dd")` or by applying a date number format to the cell. For an analyst's calendar, `=EOMONTH(start_date, n)` returns month-end, `=NETWORKDAYS(start, end, [holidays])` counts business days, `=WORKDAY(start, n, [holidays])` returns a due date skipping weekends, `=YEARFRAC(start, end, basis)` produces the fractional-year day-count familiar from swaps and bonds, and `=DATEDIF(DOB, TODAY(), "Y")` quietly computes age in years (or "YM"/"MD" for finer units). A fiscal-quarter conversion rolls month values through `(MONTH(date) - FiscalStart + 12) MOD 12 / 3, 0`. `=SUBTOTAL(9, range)` and the more flexible `=AGGREGATE(9, 5, range)` sum only visible cells after filtering, and custom number formats like `#,##0.0,,"M"` re-display a value in millions without altering the underlying number.
Pivot Tables summarize data without formulas but expose many of the analyst's most-needed operations in clicks. Right-clicking a date field and choosing Group lets the analyst collapse dates into months, quarters, or years without a date table; Value Filters → Top 10 returns the largest contributors; Value Field Settings exposes Distinct Count for unique-customer counts (`DISTINCTCOUNT(Orders[CustomerId])` in DAX), Running Total In for cumulative revenue, and Show Values As → % Difference From for prior-period comparisons. Slicers (Insert → Slicer) bind to a field and filter every connected pivot and chart on a dashboard. For pulling a single value into a downstream formula, `=GETPIVOTDATA(...)` references a pivot cell by dimension rather than by cell address, so the formula survives layout changes at the cost of verbosity.
DAX extends Excel into a relational engine. Filter context — set by slicers, page filters, and `CALCULATE` filters — determines which rows a measure aggregates; row context — created by calculated columns and iterators like `SUMX` and `AVERAGEX` — runs an expression per row. `CALCULATE([Sales], 'Date'[Year] = 2025)` is the foundation of nearly every advanced measure; chained with `ALL('Date')` it returns a "% of grand total," and the sameperiod-last-year comparison expands into a YoY% measure like `YoY% := DIVIDE([Sales], CALCULATE([Sales], SAMEPERIODLASTYEAR('Date'[Date]))) − 1`. Time intelligence ships as a family: `TOTALYTD`, `TOTALQTD`, `TOTALMTD` for running totals; `DATEADD('Date'[Date], -1, MONTH)` for the same period one month earlier; `PARALLELPERIOD(...,-1,YEAR)` for the full prior year; and a manual `CALCULATE([NetFlow], FILTER(ALL('Date'), 'Date'[Date] <= MAX('Date'[Date])))` for a cumulative balance. To activate a non-default relationship between fact and dimension (a common need for "order date" versus "ship date"), `CALCULATE` paired with `USERELATIONSHIP(...)` chooses which side of the relationship to honor; `DIVIDE(num, den, alt)` is preferred over `/` to suppress divide-by-zero artifacts. Standard risk measures crystallize from the same primitives — Sharpe as `(AVERAGE − Rf) / STDEV.S · √periods` (252 for daily, 12 for monthly), Sortino the same but with `STDEV.S(IF(returns