Power Platform Building an End to End Business Solution
Everything in this course has been a piece of a larger puzzle. This topic puts all the pieces together. You will follow the complete design and build of a real business solution — an IT Help Desk system — from the first requirements conversation to a working, deployed solution using Power Apps, Power Automate, Power BI, and Copilot Studio in combination. This is the capstone project that shows how the platform's four tools complement each other in one coherent system.
The Business Problem
A mid-sized company's IT support team receives requests through a mix of email, WhatsApp messages, and verbal requests in the corridor. There is no tracking, no prioritization, no accountability, and no visibility into how long issues take to resolve. The IT manager cannot report on SLA compliance, common issue types, or team workload. New employees do not know how to submit a request at all.
The Solution Vision
IT HELP DESK SOLUTION — COMPONENTS
┌─────────────────────────────────────────────────────────────────┐
│ SOLUTION COMPONENTS │
├────────────────┬─────────────────┬──────────────┬───────────────┤
│ POWER APPS │ POWER AUTOMATE │ POWER BI │ COPILOT │
│ │ │ │ STUDIO │
│ Canvas App: │ Notification │ IT Operations│ IT Assistant │
│ Employee app │ Flow │ Dashboard │ bot (Teams) │
│ to submit and │ │ │ │
│ track tickets │ Approval Flow │ SLA Report │ Answers FAQs │
│ │ (for High prio) │ │ │
│ Model-Driven │ SLA Breach │ Team │ Submits │
│ App: Agent │ Alert Flow │ Workload │ tickets via │
│ workspace │ │ Report │ conversation │
│ │ Auto-assign │ │ │
│ │ Flow │ │ │
└────────────────┴─────────────────┴──────────────┴───────────────┘
│
DATAVERSE (shared data layer)
├── Service Requests table
├── Assets table
├── Categories table
└── SLA Rules table
Phase 1: Data Model Design (Dataverse)
Before opening any Power Platform tool, design the data model on paper. Rushing to build an app before the data structure is clear creates rework — tables you later realize are wrong, missing columns, relationships you forgot to add.
Core Tables
SERVICE REQUESTS TABLE ┌──────────────────────┬──────────────────┬────────────────────┐ │ Column │ Type │ Notes │ ├──────────────────────┼──────────────────┼────────────────────┤ │ Title (primary col) │ Text (required) │ Brief summary │ │ Description │ Multiline text │ Full details │ │ Category │ Lookup → Category│ Hardware/SW etc │ │ Priority │ Choice │ Low/Med/High/Crit │ │ Status │ Choice │ Open/In Prog/Closed│ │ Submitted By │ Text (auto) │ From User() │ │ Submitted By Email │ Email (auto) │ From User() │ │ Assigned To │ Text │ Agent name │ │ Assigned To Email │ Email │ Agent email │ │ Due Date │ Date and Time │ Based on SLA │ │ Resolution Notes │ Multiline text │ How it was fixed │ │ Resolved On │ Date and Time │ When closed │ │ Asset (optional) │ Lookup → Assets │ Affected device │ │ Attachment │ File │ Screenshot etc │ └──────────────────────┴──────────────────┴────────────────────┘ CATEGORIES TABLE (simple reference) Category Name, Description, Default Priority, SLA Hours ASSETS TABLE (company devices) Asset Tag, Device Type, Manufacturer, Model, Assigned To Employee
Business Rules on Service Requests
Add these business rules in Dataverse: when Status changes to "Closed," make Resolution Notes required. When Priority is "Critical," make Due Date required and set the Assigned To field to be required. These rules enforce data quality at the database level regardless of which app or flow sets the values.
Phase 2: Building the Employee Canvas App
The employee-facing app has three screens: Browse (see all my requests), New Request (submit a request), and Detail (view one request's status).
Screen 1: My Requests (Browse)
MY REQUESTS SCREEN
┌─────────────────────────────────────────────────────┐
│ IT Help Desk Hello, {User().FullName} │
├─────────────────────────────────────────────────────┤
│ [🔍 Search requests...] [+ New Request] │
├─────────────────────────────────────────────────────┤
│ ● Printer on Floor 3 not working HIGH OPEN │
│ Submitted: 02 Jun 2025 Due: 04 Jun │
├─────────────────────────────────────────────────────┤
│ ○ Email password reset LOW CLOSED │
│ Submitted: 28 May 2025 Resolved: 29 May │
├─────────────────────────────────────────────────────┤
│ ● VPN not connecting MED IN PROG │
│ Submitted: 01 Jun 2025 Due: 03 Jun │
└─────────────────────────────────────────────────────┘
Gallery Items formula:
SortByColumns(
Search(
Filter('Service Requests', 'Submitted By Email' = User().Email),
SearchBox.Text, "Title"
),
"createdon", SortOrder.Descending
)
Status badge color formula (on colored rectangle):
Switch(
ThisItem.Status,
"Open", RGBA(220, 53, 69, 1), // Red
"In Progress", RGBA(255, 193, 7, 1), // Amber
"Closed", RGBA(40, 167, 69, 1), // Green
RGBA(108, 117, 125, 1) // Grey (default)
)
Screen 2: New Request Form
A simple Edit Form connected to the Service Requests table with a custom layout: Title (text input), Category (dropdown — Items: Choices('Service Requests'.Category)), Priority (dropdown), Description (multiline text input), Attachment (attachment control). The Submit button runs:
Submit button OnSelect:
If(
IsBlank(TitleInput.Text) Or IsBlank(CategoryDrop.Selected.Value),
Notify("Please fill in Title and Category.", NotificationType.Error),
// Valid — save the record
Patch(
'Service Requests',
Defaults('Service Requests'),
{
Title: TitleInput.Text,
Category: CategoryDrop.Selected,
Priority: PriorityDrop.Selected.Value,
Description: DescriptionInput.Text,
Status: "Open",
'Submitted By': User().FullName,
'Submitted By Email': User().Email
}
);
Notify("Your request has been submitted!", NotificationType.Success);
Navigate(BrowseScreen, ScreenTransition.Slide)
)
Phase 3: Building the Agent Model-Driven App
IT agents need a different interface — one that shows all requests (not just their own), lets them filter and sort by priority and status, and gives them quick access to all request details including related assets and history. A model-driven app serves this perfectly.
Views for Agents
Create these views in the Service Requests table: All Open Requests (filter Status = Open, sorted by Priority descending then Created On ascending), My Assigned Requests (filter Assigned To Email = current user, Status not Closed), SLA Breached (filter Due Date less than today AND Status not Closed), Critical Requests (filter Priority = Critical). These views appear in the model-driven app's left navigation and let agents switch between different perspectives of the same data instantly.
Business Process Flow for Resolution
SERVICE REQUEST RESOLUTION BPF [TRIAGE] → [INVESTIGATE] → [RESOLVE] → [CLOSE] TRIAGE stage required steps: ✓ Priority confirmed ✓ Category verified ✓ Assigned To set INVESTIGATE stage required steps: ✓ First response sent to user ✓ Asset identified (if hardware issue) RESOLVE stage required steps: ✓ Resolution Notes filled in CLOSE stage: ✓ Confirmed with user Status changes to Closed automatically via flow
Phase 4: Power Automate Flows
Flow 1: New Request Notification
TRIGGER: When a row is added (Dataverse — Service Requests)
│
▼
ACTION: Get manager of submitter (Office 365 Users)
│
▼
ACTION: Post to Teams channel (IT-Support → Notifications)
"🎫 New IT Request: {Title}
From: {Submitted By} | Priority: {Priority}
[Open in App]"
│
▼
CONDITION: Priority = Critical?
YES → Post URGENT Teams direct message to IT Manager
Send email to IT Manager with high importance flag
NO → (end)
Flow 2: Auto-Assign Based on Category
TRIGGER: When a row is added (Dataverse — Service Requests)
│
▼
ACTION: Get row (Dataverse — Categories table)
Filter: Category Name = [Category from trigger]
│
▼
ACTION: Patch (Dataverse — Service Requests)
Assigned To: [Default Agent from Categories row]
Assigned To Email: [Default Agent Email from Categories row]
Due Date: addHours(utcNow(), [SLA Hours from Categories row])
Flow 3: SLA Breach Alert (Scheduled)
TRIGGER: Recurrence — every day at 7:00 AM
│
▼
ACTION: List rows (Dataverse — Service Requests)
Filter: Status ne 'Closed' and DueDate lt utcNow()
│
▼
CONDITION: Output array is not empty?
YES → Apply to each breached request:
Post Teams message to IT Manager:
"⚠️ SLA BREACHED: {Title} | Assigned: {Assigned To}
Due was: {Due Date} | Priority: {Priority}"
NO → (end — no breaches today)
Flow 4: Closure Confirmation Email
TRIGGER: When a row is modified (Dataverse — Service Requests)
Filtering attribute: Status
│
▼
CONDITION: Status changed to "Closed"?
YES → Send email to Submitted By Email:
Subject: "Your IT Request Has Been Resolved — {Title}"
Body: "Hi {Submitted By}, your request has been resolved.
Resolution: {Resolution Notes}
If the issue persists, reply to this email."
NO → (end)
Phase 5: Power BI Dashboard
Connect Power BI Desktop to the Dataverse Service Requests table. Build a star schema with a Date dimension. Create these measures and visuals on three pages.
PAGE 1: OPERATIONS OVERVIEW
Card: Total Open Requests = COUNTROWS(FILTER('Service Requests', Status = "Open"))
Card: Critical Open = (filtered to Critical priority)
Card: SLA Breach Count = (filter DueDate < TODAY() and Status != Closed)
Card: Avg Resolution Hours = AVERAGEX(closed requests, DateDiff hours)
Bar Chart: Requests by Category (this week)
Line Chart: Daily request volume (last 30 days)
Slicer: Date range
PAGE 2: AGENT WORKLOAD
Table: Agent | Open Requests | Avg Age (days) | Critical Count
Bar Chart: Closed requests per agent (last 7 days)
Slicer: Agent name
PAGE 3: SLA COMPLIANCE
KPI: SLA Compliance % = % of requests resolved within SLA hours
Donut: Resolved within SLA vs. Breached
Table: All breached requests with age and assigned agent
Phase 6: Copilot Studio Bot (IT Assistant)
Build a Teams bot that lets employees check their request status and submit new requests through conversation.
BOT TOPICS
Topic: Check Request Status
Trigger: "check my ticket", "status of my request", "what happened to my IT issue"
Bot: "I'll look up your requests. One moment..."
[Call flow: Get My Requests (filters by user email, returns last 5 open requests)]
Bot: Shows Adaptive Card listing requests with status badges
Bot: "Would you like details on any of these?"
Topic: Submit New Request
Trigger: "submit a request", "I have an IT problem", "something is broken", "need help"
Bot: "I'll help you submit a request. What's the issue?"
User: types description
Bot: "What category does this fall under?"
[Shows buttons: Hardware | Software | Network | Access | Other]
User: selects category
Bot: "How urgent is this?"
[Shows buttons: Low | Medium | High | Critical]
User: selects priority
Bot: "Got it. Let me submit that..."
[Call flow: Create Service Request in Dataverse]
Bot: "Done! Your request IT-2025-{number} has been submitted.
Expected response time: {SLA Hours from category} hours."
Phase 7: Solution Packaging and Deployment
Package everything into a solution named "IT Help Desk v1.0" with your organization's publisher prefix. Include the canvas app, the model-driven app, all four flows, connection references (Dataverse, Outlook, Teams, Office 365 Users), environment variables (SharePoint site URL for any document libraries, IT Manager email address), the Copilot Studio bot, Dataverse tables, security roles (IT Agent, IT Manager, Employee), and the Business Process Flow. Export as managed. Deploy to TEST for user acceptance testing. After sign-off, deploy to PROD. Publish the Power BI report to the IT team workspace and share the dashboard with the IT manager.
Key Points
- Design the Dataverse data model first, before opening any app or flow builder. The quality of the data model determines the quality of everything built on top of it.
- Canvas apps serve employees who need a mobile-friendly, simple submission interface. Model-driven apps serve agents who need structured, relationship-aware data management.
- Power Automate handles all the invisible work: notifications, auto-assignment, SLA alerting, and closure confirmation — making the system feel intelligent without any code.
- Power BI converts accumulated request data into actionable management insights: workload, SLA compliance, category trends.
- Copilot Studio brings the system to Teams — employees interact naturally in conversation rather than opening a separate app.
- Package the entire solution — apps, flows, bot, tables, security roles, connection references, and environment variables — into a versioned solution for clean, repeatable deployment across environments.