Copilot in Excel and Power BI
Phase 5 goal
Use AI to speed up every layer you've learned (formulas, M, DAX, flows, Python, narrative) while keeping data security airtight and checking every output.
The golden rule (government and defense): Only use AI tools your organization has approved, for the data types they are approved for. Never put CUI, PII, proprietary, export-controlled, source selection, pre-decisional, or classified information into public or consumer AI tools. When unsure, use schema-only prompts with fictional sample data, and ask your security or CUI office.
🎯 Learning objectives
Tick each one when you can actually do it.
💡 Key concepts
- Copilot in Excel: Works on data in your workbook (best with Tables). Supports asking for insights, formula columns, PivotTables/charts, highlighting/sorting/filtering. Newer versions offer edit/plan/chat modes. Review every change, and Undo/version history are your safety net. Availability depends on license and tenant settings.
- Copilot in Power BI: Report pane Q&A and summaries, report page creation, DAX query writing in DAX query view, measure descriptions. Requirements: paid Fabric (F2+) or Premium (P1+) capacity, admin enablement, supported region. Not supported in sovereign clouds (per Microsoft docs at time of writing).
- Model readiness drives quality: Clear table and column names ("Actual Obligation" not "ACT_OBL_AMT"), measure descriptions, hidden technical columns, synonyms, and verified answers. That's good modeling practice anyway (Module 2.3).
- If Copilot isn't available to you: Use the same prompting skills with your organization's approved AI tool on schemas (Module 5.2). Still worth learning, because availability in government tenants changes.
🛠 Hands-on exercise: Copilot Test Drive (or Simulation)
Data: fictional budget_execution (as an Excel Table) and your PEO-FS Power BI model.
- Excel: Ask Copilot to (1) add an
ObligationRatecolumn, (2) build a PivotTable of FYTD obligations by Appropriation, (3) highlight programs under 80% of plan, (4) identify outliers. Check each result manually. - Power BI: Ask Copilot to (1) create a page summarizing execution by portfolio, (2) write a DAX query listing programs with CPI < 0.95, (3) write descriptions for 5 measures. Compare against your own measures.
- No Copilot access? Do the same tasks with an approved general AI tool using schema-only prompts, then build the results yourself.
📦 Mini-project: Copilot Readiness Pass
Run a "prep for AI" pass on your PEO-FS model: rename cryptic fields, add descriptions to all measures (Copilot or AI-drafted, human-reviewed), hide keys, add synonyms (e.g., "spend" → Actual Expenditure, "cost efficiency" → CPI Cum), and write 5 test questions with known correct answers to evaluate Copilot (or any Q&A feature) responses.
🧠 Self-check quiz
Answer in your own words, then check. Grade yourself honestly; your score is saved.