Integrating Dynamics 365 with Power Platform: Power Apps, Power Automate, and Power BI

Integrating Dynamics 365 with Power Platform: Power Apps, Power Automate, and Power BI

Introduction

Power Platform seamlessly integrates with Dynamics 365, enabling citizen developers and pro developers to extend CRM and ERP capabilities without code or with low-code solutions. This guide covers building custom apps with Power Apps, automating workflows with Power Automate, creating interactive dashboards with Power BI, and leveraging virtual tables for Finance & Operations integration.

Power Platform Architecture

Integration Overview

Platform connectivity:

┌──────────────────────────────────────────────────┐
│               Power Platform                      │
│  ┌─────────────┐  ┌──────────────┐  ┌─────────┐ │
│  │ Power Apps  │  │Power Automate│  │Power BI │ │
│  └─────┬───────┘  └──────┬───────┘  └────┬────┘ │
│        │                  │                │      │
│        └──────────────────┴────────────────┘      │
│                           ↓                       │
│        ┌──────────────────────────────────┐      │
│        │   Microsoft Dataverse            │      │
│        │   (Common Data Service)          │      │
│        └──────────┬──────────────┬────────┘      │
└───────────────────┼──────────────┼───────────────┘
                    ↓              ↓
        ┌───────────────────┐  ┌──────────────────┐
        │  Dynamics 365 CE  │  │ Dynamics 365 F&O │
        │  (Sales, Service) │  │ (via Virtual     │
        │                   │  │  Tables)         │
        └───────────────────┘  └──────────────────┘

Dataverse as Integration Hub

Benefits of Dataverse:

• Unified Data Model: Standard tables (Account, Contact, etc.) shared across D365 and Power Platform
• Security: Inherited role-based access control from Dynamics 365
• Business Logic: Plugins, workflows, and business rules apply consistently
• Relationships: Navigate related data (Account → Contacts → Opportunities)
• Audit & Change Tracking: Track data modifications automatically

Power Apps Integration

Model-Driven Apps

Extending Dynamics 365 Sales:

App Name: Project Management for Sales
Purpose: Track projects linked to opportunities

Tables Included:
  - Opportunity (Standard)
  - Account (Standard)
  - Contact (Standard)
  - Project (Custom)
  - Project Task (Custom)
  - Project Resource (Custom)

Site Map Structure:
  Sales:
    - Dashboard
    - Opportunities
    - Accounts
    - Contacts
  Projects:
    - Active Projects
    - My Projects
    - Project Tasks
    - Resource Allocation
  Reports:
    - Project Pipeline
    - Resource Utilization

Forms:
  Project Main Form:
    Sections:
      - General Information
        - Project Name
        - Customer (Lookup to Account)
        - Opportunity (Lookup to Opportunity)
        - Project Manager (Lookup to User)
        - Start Date / End Date
      - Financial
        - Estimated Revenue
        - Actual Revenue
        - Budget
        - Actual Cost
      - Resources (Subgrid: Project Resources)
      - Tasks (Subgrid: Project Tasks)

Business Rules:
  - If Status = "In Progress", make Project Manager required
  - If Actual Cost > Budget, show warning
  - Calculate Total Estimated Hours from tasks

Creating model-driven app:

1. Navigate to make.powerapps.com
2. Select Apps → New app → Model-driven
3. Add tables, forms, views, and charts
4. Configure site map and app designer
5. Publish and share with security roles

Canvas Apps Embedded in Dynamics 365

Opportunity canvas app component:

App Name: Opportunity Risk Assessment
Type: Canvas App
Embedded In: Opportunity form (D365 Sales)

Data Sources:
  - Dynamics 365 (Opportunities)
  - Dataverse (Risk Factors custom table)
  - SharePoint (Document library)

Controls:
  Risk Score Gauge:
    Formula: |
      Sum(
        Filter(RiskFactors, OpportunityId = OpportunityRecord.Id),
        RiskLevel * Probability
      )
    Color: If(RiskScore > 70, Red, If(RiskScore > 40, Orange, Green))
  
  Risk Factors Gallery:
    Items: Filter(RiskFactors, OpportunityId = OpportunityRecord.Id)
    Template:
      - Label: Factor Name
      - Dropdown: Risk Level (Low/Medium/High)
      - Slider: Probability (0-100%)
  
  Mitigation Actions:
    Items: Filter(MitigationActions, OpportunityId = OpportunityRecord.Id)
    OnSelect: Navigate to action details
  
  Add Risk Button:
    OnSelect: |
      Patch(RiskFactors,
        Defaults(RiskFactors),
        {
          OpportunityId: OpportunityRecord.Id,
          Name: TextInput_RiskName.Text,
          RiskLevel: Dropdown_Level.Selected.Value,
          Probability: Slider_Probability.Value
        }
      )

Embedding canvas app in model-driven form:

1. Open Dynamics 365 form editor
2. Insert Canvas App control
3. Select existing app or create new
4. Pass context: OpportunityRecord → Canvas app variable
5. Set height and width
6. Publish form

Power Apps Portals (Power Pages)

Customer self-service portal:

Portal: Customer Service Portal
Purpose: Allow customers to view cases, submit requests, access knowledge base

Authentication:
  - Azure AD B2C
  - Local authentication
  - Social providers (Google, Facebook)

Web Pages:
  Home:
    - Welcome message
    - Quick links
    - Recent announcements
  
  My Cases:
    - List view filtered by contact
    - Create new case
    - View case details and timeline
    - Add attachments
  
  Knowledge Base:
    - Search articles
    - Browse by category
    - Rate articles (helpful/not helpful)
  
  Profile:
    - Update contact information
    - Change password
    - Communication preferences

Security:
  - Web Roles: Authenticated Users, Premium Customers
  - Table Permissions:
    - Case: Read/Write own records
    - Knowledge Article: Read published articles
    - Contact: Read/Write own contact

Custom Development:
  - Liquid templates for page layout
  - Web API for complex queries
  - JavaScript for client-side logic

Power Automate Integration

Cloud Flows for Dynamics 365

Example 1: Opportunity won automation:

Trigger: When a record is updated (Dataverse)
  Table: Opportunity
  Filter: Status = Won

Actions:
  1. Get Account Details:
     - Table: Account
     - Row ID: Opportunity.Customer

  2. Condition: Account Type = "High Value"
     If Yes:
       - Send email to Account Manager:
         To: Account.Owner.Email
         Subject: "High-value opportunity won: {Opportunity.Name}"
         Body: HTML template with opportunity details
       
       - Create task for onboarding:
         Table: Task
         Subject: "Onboarding - {Account.Name}"
         Due Date: Add 7 days
         Owner: Opportunity.Owner
         Regarding: Opportunity
       
       - Post to Teams channel:
         Team: Sales Team
         Channel: Wins
         Message: "🎉 {User} won {Opportunity.Name} - ${Opportunity.EstimatedRevenue}"
     
     If No:
       - Send standard email notification

  3. Update Account:
     - Last Won Opportunity Date: UtcNow()
     - Total Won Opportunities: Add 1

Example 2: Lead scoring automation:

Trigger: When a record is created or updated (Dataverse)
  Table: Lead

Actions:
  1. Initialize Score Variable: 0

  2. Add points based on criteria:
     - If Lead Source = "Website": Add 10
     - If Job Title contains "Director/VP/Manager": Add 20
     - If Company Size > 500: Add 15
     - If Industry = "Technology": Add 10
     - If Budget > $50,000: Add 25
     - If Timeline = "Immediate": Add 20

  3. Condition: Score >= 70 (Hot Lead)
     If Yes:
       - Update Lead:
         Rating: Hot
         Lead Score: {Score}
       - Assign to senior sales rep
       - Create phone call activity (due today)
       - Notify sales manager via Teams
     
     If 40 <= Score < 70 (Warm Lead):
       - Update Lead:
         Rating: Warm
         Lead Score: {Score}
       - Assign to sales rep (round-robin)
       - Create follow-up task (due in 2 days)
     
     If Score < 40 (Cold Lead):
       - Update Lead:
         Rating: Cold
         Lead Score: {Score}
       - Add to nurture campaign

Approval Workflows

Purchase order approval:

Trigger: When a record is created (Dataverse)
  Table: Purchase Order

Condition: Total Amount > $5,000

Actions:
  1. Start Approval Process:
     Approval Type: First to respond
     Approvers:
       - If Amount < $25,000: Direct Manager
       - If Amount >= $25,000: Director + Finance Manager
     Details:
       Title: "Purchase Order Approval - PO-{PONumber}"
       Description: |
         Vendor: {VendorName}
         Amount: ${TotalAmount}
         Items: {ItemList}
         Requestor: {CreatedBy.FullName}
     Timeout: 48 hours

  2. Condition: Approval Response
     If Approved:
       - Update Purchase Order:
         Status: Approved
         Approved Date: UtcNow()
         Approved By: {ApproverEmail}
       - Send notification to requestor
       - Create task for procurement: "Process PO-{PONumber}"
     
     If Rejected:
       - Update Purchase Order:
         Status: Rejected
         Rejection Reason: {Comments}
       - Send notification to requestor with reason
       - Update budget allocation (return funds)

  3. If Timeout:
     - Escalate to Department Head
     - Send reminder email

Desktop Flows (RPA)

Legacy system integration:

Flow Name: Import Customer Orders from Legacy ERP
Type: Desktop Flow (Attended/Unattended)

Steps:
  1. Launch legacy ERP application
  2. Navigate to Orders Export screen
  3. Select date range: Yesterday
  4. Click Export button
  5. Wait for CSV file generation
  6. Read CSV file from Downloads folder
  7. For each row in CSV:
     - Parse order data
     - Create Sales Order in Dynamics 365 (via Web API)
     - Update legacy system with D365 Order ID
  8. Move CSV to Archive folder
  9. Send completion report email

Schedule: Daily at 6:00 AM
Machine: RPA-SERVER-01
Credentials: Service Account
Error Handling: Retry 3 times, email admin on failure

Power BI Integration

Embedded Dashboards in Dynamics 365

Sales Performance Dashboard:

Dashboard Name: Sales Executive Dashboard
Embed Location: Dynamics 365 Sales Hub home page

Data Sources:
  - Dataverse (Direct Query)
    - Opportunity
    - Account
    - User (Sales Reps)
  - Azure SQL (Import)
    - Sales Targets
    - Historical Sales Data

Visuals:
  Row 1:
    - Card: Total Pipeline Value
      Measure: SUM(Opportunity[EstimatedRevenue])
      Filter: Status = Open
    
    - Card: Won Revenue (YTD)
      Measure: SUM(Opportunity[ActualRevenue])
      Filter: Status = Won, Year = CurrentYear
    
    - Card: Average Deal Size
      Measure: AVERAGE(Opportunity[ActualRevenue])
      Filter: Status = Won
    
    - Gauge: Win Rate
      Value: DIVIDE(CountWon, CountTotal)
      Target: 30%

  Row 2:
    - Column Chart: Pipeline by Stage
      X-Axis: Sales Stage
      Y-Axis: Sum of Estimated Revenue
      Legend: None
      Sort: By Stage Order
    
    - Funnel Chart: Opportunity Conversion
      Values: Count of Opportunities
      Groups: Lead → Qualified → Quote → Won
      Colors: Blue gradient

  Row 3:
    - Table: Top 10 Opportunities
      Columns:
        - Opportunity Name
        - Account
        - Owner
        - Estimated Revenue
        - Close Date
        - Stage
      Sort: Estimated Revenue (Descending)
      Conditional Formatting:
        - Close Date: Red if past due
        - Estimated Revenue: Green gradient
    
    - Line Chart: Revenue Trend
      X-Axis: Month
      Y-Axis: Actual Revenue
      Lines: Current Year, Last Year
      Markers: Show data points

  Row 4:
    - Map: Opportunities by Location
      Location: Account.Address
      Size: Estimated Revenue
      Tooltip: Account Name, Opportunity Name

Filters:
  - Date Range: Relative (Last 12 Months)
  - Owner: Multi-select (Sales Reps)
  - Industry: Multi-select
  - Region: Multi-select

Refresh Schedule:
  - Type: Automatic
  - Frequency: Every 30 minutes (Direct Query)
  - Credentials: Service Principal

Row-Level Security:
  - Sales Reps: See only their opportunities
  - Sales Managers: See team opportunities
  - Executives: See all opportunities

Power BI Reports for Finance & Operations

Inventory Analysis Report:

Report Name: Inventory Valuation & Turnover
Data Source: Dynamics 365 F&O (via Data Export Service)

Data Model:
  Tables:
    - InventTable (Items)
    - InventSum (Inventory on-hand)
    - InventTrans (Transactions)
    - InventSite (Sites)
    - InventLocation (Warehouses)

  Relationships:
    - InventTable[ItemId] → InventSum[ItemId]
    - InventSum[InventSiteId] → InventSite[SiteId]
    - InventTrans[ItemId] → InventTable[ItemId]

  Measures:
    Total Inventory Value:
      = SUMX(InventSum, InventSum[PhysicalQty] * InventTable[CostPrice])
    
    Inventory Turnover Ratio:
      = DIVIDE([COGS], [Average Inventory Value])
    
    Days Inventory Outstanding:
      = DIVIDE(365, [Inventory Turnover Ratio])
    
    Slow Moving Items (> 90 days):
      = CALCULATE(
          COUNTROWS(InventTable),
          InventTrans[LastMovementDate] < TODAY() - 90
        )

Pages:
  1. Overview:
     - KPI Cards: Total Value, Turnover Ratio, DIO
     - Treemap: Inventory Value by Category
     - Line Chart: Inventory Value Trend (12 months)

  2. Item Analysis:
     - Matrix: Item Group → Item → Quantity, Value, Turnover
     - Scatter Chart: Quantity vs. Value
     - Table: Slow Moving Items

  3. Location Analysis:
     - Map: Inventory by Warehouse Location
     - Stacked Bar Chart: Inventory by Site and Category
     - Heatmap: Stock Coverage by Location

  4. Transactions:
     - Line Chart: Issues vs. Receipts
     - Waterfall Chart: Net Inventory Change
     - Table: Recent Transactions

Virtual Tables for F&O Integration

Accessing F&O Data from Dataverse

Virtual table setup:

Purpose: Access Finance & Operations data in Dataverse without data replication

Configuration Steps:
  1. Install Dynamics 365 Finance & Operations virtual entity app
  2. Configure Finance & Operations connection in Dataverse
  3. Enable desired F&O entities as virtual tables

Example Virtual Tables:
  - Customers V3
  - Vendors V2
  - Released Products V2
  - Sales Orders V2
  - Purchase Orders V2

Usage in Power Apps:
  Data Source: Customers V3 (virtual table)
  Filter: |
    Filter(
      'Customers V3',
      CustomerGroupId = "WHOLESALE" && 
      SalesCurrencyCode = "USD"
    )
  
  Display: Gallery with customer details
  
  OnSelect: |
    Navigate(
      CustomerDetailScreen,
      ScreenTransition.Fade,
      { SelectedCustomer: ThisItem }
    )

Limitations:
  - No offline access (requires internet)
  - Limited to OData operations (no complex joins)
  - Performance depends on F&O availability

Best Practices

1. Security Alignment: Ensure Power Platform security roles align with D365 permissions
2. Error Handling: Implement try-catch in flows and handle API errors gracefully
3. Performance: Use Direct Query for real-time data, Import for historical analysis
4. Governance: Establish Center of Excellence (CoE) for Power Platform
5. Naming Conventions: Use consistent prefixes for custom tables, fields, and flows
6. Documentation: Document flows, apps, and reports for maintainability
7. Testing: Test integrations in sandbox before deploying to production

Troubleshooting

Power Automate flow failures:

Error: "The user with SystemUserId cannot be found"
Cause: Flow running under unlicensed or disabled user account

Solution:
  1. Open flow in edit mode
  2. Check trigger and action connections
  3. Update connections to use active service account
  4. Ensure service account has appropriate D365 license
  5. Test flow execution

Power BI refresh errors:

Error: "Unable to connect to Dataverse"
Cause: Authentication credentials expired or changed

Solution:
  1. Navigate to Power BI Service → Dataset Settings
  2. Update Data Source Credentials
  3. Use Service Principal authentication for unattended refresh
  4. Verify service principal has read permissions in Dataverse

Key Takeaways

• Power Platform extends Dynamics 365 capabilities with low-code/no-code tools
• Model-driven apps share Dataverse security and data with D365
• Power Automate enables sophisticated workflow automation across systems
• Power BI provides real-time analytics embedded in D365 experiences
• Virtual tables bridge F&O data into Dataverse for unified access

Next Steps

• Implement AI Builder models for form processing and predictions
• Create Power Virtual Agents chatbots integrated with D365 data
• Build Dual-Write synchronization between F&O and Dataverse
• Establish Center of Excellence (CoE) for governance

Additional Resources

• Power Apps with Dynamics 365
• Power Automate for Dynamics 365
• Power BI Integration
• Virtual Tables for Finance & Operations

---

Low code, high impact.