Case study · Data and automation · 2022–2026
Taking the manual work out of two Air Force jobs
In the U.S. Air Force, I built two systems on Microsoft 365. As a training technician, I built Power Automate workflows that schedule training and notify everyone involved, which cut no-shows by 97%. Later, as a plans and scheduling technician, I built a Power BI dashboard that pulls thirteen data sources into one view.
- Context
- U.S. Air Force: training technician (2022–2025), then plans and scheduling technician (2025–now)
- People
- More than 300 in the training workflows
- Tools
- Power BI, Power Query (M), Power Automate, SharePoint, Microsoft Lists
- Outcome
- About 8,000 person-hours a year recovered; 97% fewer training no-shows; workflows adopted by four other units
The problem
Training scheduling ran on email. The training office sent hundreds of reminders a week, people still missed sessions, and every missed slot had to be found, chased, and rebooked.
The scheduling job had a different problem. The data existed, but it lived in thirteen separate sources, so answering a routine question meant pulling from several of them and reconciling the results by hand. What the dashboard tracks isn’t public, so this page covers how it was built, not what it shows.
Constraints
- Everything had to run inside tools the organization already approved: Microsoft 365 and the Power Platform. No new servers and no outside software.
- The people using the result were busy supervisors, not analysts. It had to answer their question at a glance.
- Training records are an audit trail, so history had to be kept, not overwritten.
Decisions
Clean the data in Power Query, before any dashboard
- What
- All thirteen sources go through Power Query transformations, much of it written directly in M in the Advanced Editor, before reaching the data model.
- Why
- Thirteen sources meant thirteen sets of conventions. Doing the cleaning once, as code, means every refresh repeats it exactly and the dashboard can be trusted.
- Considered
- Cleaning each export by hand before loading it.
- Accepted tradeoff
- More work up front, and queries that someone has to understand in order to maintain.
Act on the data, not only report it
- What
- Scheduling a session starts with a Microsoft Form. Submitting it triggers a Power Automate flow that notifies everyone the session affects: the member, their supervisor, their shop, and anyone else who needs to know. If the member still misses it, the session is flagged and listed for review at the next scheduling meeting.
- Why
- A dashboard shows who is overdue. It doesn’t get anyone to their appointment. The 97% drop in no-shows came from closing that gap.
- Considered
- Manual reminders sent by the training office, which meant hundreds of emails a week.
Archive automatically
- What
- Active records live in Microsoft Lists, and a Power Automate flow moves completed items to an archive on a schedule.
- Why
- The working list stays short and fast for daily use, while the full history stays intact for audits.
- Accepted tradeoff
- One more flow to monitor. If it stops, the working list slowly fills up.
What broke
The flows break when their credentials expire. The Air Force has strict credential rotation requirements, and when a connection’s credentials lapse, the flow retries a few times and then stops on its own. Nothing tells anyone it stopped.
The flows run on my credentials, so there’s a real gap here between automation and security. I made it a personal policy to renew my credentials every week. That keeps the flows running while I’m in the organization, without giving them access that outlasts me if I leave.
Results
- About 8,000 person-hours a year recovered across the organization.
- Training no-shows down 97%.
- Four other units adopted the training workflows.
What I’d change
I’d write much clearer documentation, both for building the system and for using it. I understand what I built, but the people who inherit it have to understand it too. A good handoff is the difference between changes that stick and a shop going back to hand-jamming because it’s familiar.