Power Query in Excel

💡 Key concepts

• ETL mindset: Extract (Get Data) → Transform (Power Query Editor) → Load (Table, PivotTable, or Data Model). The steps are recorded, so next month you just click Refresh.
• Applied Steps pane. Each step is a line of M.
• Essential transforms: Promote headers, Remove top rows, Change type, Trim/Clean, Replace values, Split column, Fill down, Conditional column, Remove duplicates, Group By.
• Unpivot: Turns Oct | Nov | Dec columns into Month | Value rows (the most important reshape for budget data).
• Merge vs. Append: Merge = join (like XLOOKUP for whole tables: Left Outer, Inner, Anti for "what's missing"). Append = stack tables with the same columns.
• From Folder: Combine every monthly status file automatically, so a new file dropped into the folder gets picked up on refresh.
• Data types and locale: Set types explicitly. Use "Using Locale" for odd date formats.
• Error handling: "Replace Errors", "Keep Errors" (to audit), and in M, try … otherwise.
• Parameters: Store the folder path as a parameter so the workbook is portable.

🛠 Hands-on exercise: Tame the Status Drops

Data: status_drops/ folder (D8). Headers vary; columns roughly include ProgramID, Milestone, Fcst Dt / Forecast Date, RAG, Cost columns as text, month columns Oct–Sep.

1. Data → Get Data → From Folder → Combine & Transform.
2. In the sample-file transform: remove the top 3 title rows, promote headers, and remove blank rows.
3. Standardize headers: rename variants to ForecastDate (tip: one rename step with a list, or the Table.RenameColumns MissingField.Ignore option. Look at it in the Advanced Editor).
4. Clean RAG: Trim, Uppercase, then map G/GREEN/GRN → Green, etc. (Conditional Column or Replace Values).
5. Dates: convert text to date. Turn TBD and Q3 FY26 into null (Replace Errors → null). Add a DateQualityFlag column.
6. Money: write a custom column that converts $1.2M, 450K, 1,250,000 into numbers:

   let t = Text.Upper(Text.Remove([CostText], {"$", ",", " "})),
       mult = if Text.EndsWith(t, "M") then 1000000 else if Text.EndsWith(t, "K") then 1000 else 1,
       num  = try Number.From(Text.TrimEnd(t, {"M","K"})) otherwise null
   in  if num = null then null else num * mult
   

7. Unpivot the Oct–Sep columns into FYMonthName / Amount, then add FYMonth (Oct=1 … Sep=12).
8. Merge with programs to bring in Portfolio. Load to a Table named StatusClean.
9. Drop a new month's file into the folder and Refresh. Confirm it shows up.

📦 Mini-project: Contracts Health Query

Build a query pipeline that (a) merges contracts with a rollup of budget_execution obligations, (b) uses an Anti join to list contracts with no obligation activity in the last 90 days, and (c) adds PoPDaysRemaining and % Ceiling Obligated. Load it to a formatted Table plus a small summary.