Prompting LLMs for Formulas, M, DAX, Flows, and Python
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
- The analytics prompt template:
ROLE: You are an expert in [Excel 365 / Power Query M / DAX / Power Automate / Python pandas]. CONTEXT: I'm building [monthly EVM report]. Federal fiscal year runs Oct–Sep. SCHEMA: Table EVM: PeriodEnd (date, month-end), BCWS, BCWP, ACWP (currency, current-period), BAC (currency, repeats each period). Date table: dim_Date[Date], marked as date table. TASK: Write a measure for cumulative CPI and EAC = BAC / CPI_cum. RULES: Use VAR/RETURN, DIVIDE for safety, no deprecated functions, take BAC from latest period only. TEST: With these 3 fictional rows [...], the expected CPI_cum is 0.92. OUTPUT: Code first, then a 3-bullet explanation, then edge cases to watch out for. - Tool-specific tips:
- Excel: state your version ("Microsoft 365, dynamic arrays available. GROUPBY not available"). Ask for LET for readability.
- M: paste your Advanced Editor code (sanitized) and the exact error message.
- DAX: describe relationships and which are active. Say whether the date table is marked.
- Power Automate: ask for the expression and where to put it. Name your actions in the prompt.
- Python: specify the pandas version, ask for type hints, docstrings, and a small test.
- Iterating: Paste the error back exactly. Ask "explain line by line". Ask for 2 alternative approaches with trade-offs.
- Known failure modes: Invented functions (fake DAX/M function names), outdated syntax, silent logic errors (wrong grain, averaging ratios), ignoring the fiscal year, answers that sound confident but are wrong.
- Learning, not outsourcing: Ask AI to teach: "Give me a hint, not the answer" during exercises.
🛠 Hands-on exercise: Prompt Kata (5 rounds)
Use an approved tool with fictional schemas only. For each task, write a structured prompt, run the output, and record whether it worked first time:
- Excel: a LAMBDA
WORKDAY_SLIP(baseline, forecast, holidays). - M: a function that standardizes RAG values (G/Green/grn → Green) and flags unknowns.
- DAX:
Milestones Completed On Time %using the inactive ActualDate relationship. - Power Automate: an expression that returns the federal fiscal quarter label ("FY26 Q1") for today.
- Python: a pandas function computing months-of-slip per milestone and bucketing it. Log each in a "prompt journal" table: Task | Prompt | Worked? | Fix needed | Lesson.
📦 Mini-project: Personal Prompt Library
Build a reusable prompt library (Markdown or OneNote) with 10 templates you'll actually use at work: formula writer, M debugger, DAX explainer, measure reviewer, flow expression helper, pandas cleaner, docx report builder, code reviewer, "explain this coworker's script", and status-narrative drafter (fictional or approved-tool data only).
🧠 Self-check quiz
Answer in your own words, then check. Grade yourself honestly; your score is saved.