When people ask what makes Power Platform powerful, they point to the tools. Power Apps for the interface. Power Automate for the workflow. Copilot Studio for the agent. After a couple of years of building with it, I think the answer sits somewhere else.
The power is in what the platform rides on.
Power Platform sits on Azure, Microsoft 365, Entra ID, and Dataverse. Identity is solved. Security is inherited. Your users already live in these tools. So the useful question stops being “what can this tool do?” and becomes “what does the Microsoft cloud already do that I can pull in?”
Here is what that looked like in practice.
A word about connectors
In Power Automate, almost everything is a connector. SharePoint is one. Outlook is one. The HTTP action is one too, so calling an Azure Function is still using a connector.
The difference is what each one gives you. A prebuilt connector wraps a service and hands you actions someone else already built: get a file, send a message, create a row. The HTTP action gives you only a pipe. It carries a request out and a response back, and it does no thinking in between.
That distinction shapes everything below. Connectors move data between systems. They don’t interpret it. When a solution needs something understood, calculated, or reshaped beyond what the actions allow, the logic has to live somewhere else, and the flow reaches it through that pipe. Each section that follows is a different answer to the same question: where should this work live?
Power Platform is the front door. Everything beneath the pipe is the Microsoft cloud doing the work.
When no connector understands the work
I needed to run construction takeoffs from CAD drawings. A drawing arrives as a DXF file, and the questions are practical ones. How much floor? How much wall? How many doors? No connector I know of opens a drawing and reports its geometry.
An Azure Function does. It uses a Python library called ezdxf. The flow sends the file over HTTP, the function reads the drawing and walks every entity, the lines, arcs, polylines, hatches, and text labels, and it returns structured JSON: counts by type, total length, enclosed area, and the room annotations. One hospital floor plan came back with more than fifty thousand entities, summarized in a payload a flow can handle.
Then the flow goes back to what it does well. It stores the result, routes it for review, and notifies the right person.
Two lessons came out of it. Files travel in more shapes than you expect. The content can arrive wrapped in JSON, as raw base64, or as a stringified byte object, and my function had to learn to accept all three. And know what your library can read. ezdxf handles DXF and DWG, not DWF, so a different file format meant a different step before the function ever ran.
When the loop is the problem
Some flows turn into nested loops with actions stacked inside each one. They work, but they run slowly and they are painful to maintain.
I moved that logic into functions. One example compared two lists of tasks, treating one as the source of truth, and returned three things: items to update, items to add, items to remove. The flow’s only job was to apply those results through an HTTP call. The function did the thinking and the flow did the delivering.
It also costs less than people expect, and the setup is modest. Secrets go in Key Vault, and the function reads them through a managed identity. The API key never appears in the flow.
When the work outgrows a flow
Contract analysis is one of my favorite examples. A user uploads a PDF to a record in Dynamics 365. A Logic App watches that table, notices the file, downloads it in chunks, and sends it to an Azure Function for analysis. The function is given thirty minutes, because contracts are not quick reading. The result is cleaned, written back to the record, and the user gets an in app notification.
The Power Platform front end stayed simple. The user uploads a file and receives an answer. Behind it, a Logic App, a Function, and a vector store did the heavy work. Nobody on the business side needed to know.
When the answer lives in Excel
This is the one people overlook, and it fits the definition in an interesting way. Running an Office Script from a flow is itself a connector action, the Run script action in the Excel Online connector. The connector is only the pipe. The script is where the work happens.
In one solution, scripts read the column headers from an uploaded file and from the target import template. An AI prompt mapped one to the other, and a final script wrote the clean CSV. A practical note from that build: return arrays from a script as JSON strings. Power Automate will not surface a native array as usable dynamic content, and you will lose an afternoon finding that out.
In another, a script wrote summary values to fixed cells and built a named table, while the bulk rows went in through the Microsoft Graph API using the HTTP with Entra ID connector. Excel stayed the output, and the platform did the rest.
One caution. Office Scripts can be switched off in a tenant, and mine was at first. Know what your environment allows before you design around it.
What reaching costs
Every piece of this has a price, and I do not mean only the invoice.
Logic Apps Standard needs a storage account. Managed identities need role assignments on every resource they touch. Choose a model in Foundry without understanding its pricing, and you can pay for reasoning tokens to summarize an email. I have seen that mistake multiply a cost by seven. I have also cost an organization 23,000 dollars in a single day through one careless configuration. The lesson was simple. Ask questions before you configure, of your stakeholders and of yourself.
Reaching into the platform is worth it. It just is not free, and it rewards builders who know what they are reaching for.
The shift
A solution built on one tool inherits that tool’s limits. A solution built on a platform inherits the platform’s reach.
Power Platform is the front door. The Microsoft cloud is the building behind it. Good architects learn to use the whole building.
One of the fastest ways to build a bad platform strategy is to turn a platform you like into the answer for every problem.
That is usually where good intentions begin creating long term complexity.
The purist answer is, “We can build it.” The practitioner’s answer is more useful: let products do what they are already good at, and be intentional about what you add around them.
That matters even more now. AI is changing how people interact with business systems, and Power Platform can connect to APIs, events, data sources, and existing applications. In that world, rebuilding a product’s core capability is rarely as inventive as it sounds. More often, it means recreating something the organization already owns, then accepting the responsibility to maintain it forever.
For example, I do not need to build a full project management product in Dataverse just because I want better delivery governance. The project management platform can continue to own schedules, tasks, assignments, and dependencies. Dataverse can own the governed operational work around it: engagement health, delivery risks, decisions, approvals, exceptions, portfolio reporting, and the automations that connect delivery activity to leadership and other systems.
Dataverse does not need to be everything.
It needs to be the right thing: the governed operational layer for work that requires structure, ownership, security, workflow, automation, and reuse across the organization.
That is the real decision in front of us.
Not, “Can Dataverse store this?”
It usually can.
The better question is, “Does this work need the kind of governed, connected, actionable operational data platform that Dataverse provides?”
Some work needs a place to be discussed. Some work needs a place to be documented. Some work needs a place to be analyzed.
But some work needs to be owned.
It needs structure. It needs a lifecycle. It needs accountability. It needs to move through a process, trigger action, respect security boundaries, and remain available to the next application, flow, report, integration, or person that depends on it.
That is the kind of work that belongs in Dataverse.
Look for the Signals
There will be signs.
Including Dataverse in a solution architecture should be an intentional decision, not a reflex. Certain signals tell us when work has outgrown a simple list, spreadsheet, or lightweight collaboration tool.
And, to be clear, these signals are not exclusive to Dataverse.
Meaningful relationships. A defined lifecycle. Different levels of access. Automation. Auditability. Reuse across applications and systems. Data that drives action or decisions.
Those signals do not automatically mean, “Use Dataverse.”
They mean the work deserves an intentional operational architecture.
Dataverse may be that architecture. Another product may already own the domain. Or Dataverse may become the governed layer around the system that remains authoritative.
The question is not whether Dataverse can store the data. The question is what should own the work, and what role Dataverse should play around it.
When several of these conditions appear together, the work is telling you something. It is no longer just information someone wants to keep. It is becoming an operational process: something that coordinates people, carries responsibility, moves through stages, triggers decisions, and needs to remain dependable after the original maker has moved on.
That is when we need to slow down and look carefully at the data, the process, the people involved, and the systems that already own part of the work.
A workstream may be ready for a more intentional operational foundation when several of the following statements are true.
Signal
What it means
Meaningful relationships
Start with the data model. Does the work involve more than a flat list? Are there customers, projects, requests, approvals, tasks, risks, decisions, and documents that need to relate to one another?
A defined lifecycle
Does the record move through recognizable states, handoffs, decisions, exceptions, or approvals that the business needs to understand and trust?
Different responsibilities require different access
Do different people need different levels of control over who can create, view, update, approve, assign, share, delete, or export the information?
The process needs dependable action
Do status changes, thresholds, dates, or decisions need to trigger automation, notify people, update another system, or create the next step in the process?
The organization needs accountability
Does the business need to know who changed something, when it changed, and sometimes why a decision was made?
The same record must be reused
Does the same record need to appear in multiple apps, flows, reports, agent experiences, or integrations without creating competing versions of the truth?
The data drives decisions
Is the information used to approve funding, escalate a delivery issue, manage risk, prioritize work, or run part of the business?
The presence of these signals is not a shortcut to a product decision. It is a prompt to design intentionally.
Sometimes Dataverse should be the foundation. Sometimes the product already built for the domain should remain the system of record. And sometimes the best architecture is a partnership: the authoritative system continues to own the core domain, while Dataverse owns the intake, governance, approvals, exceptions, coordination, reporting, and automation that make the work manageable across the organization.
That is the measure of a healthy architecture: each platform has a clear job, each important record has a clear owner, and the organization can explain why the work lives where it lives.
Work Dataverse Owns Directly
As I mentioned earlier, Dataverse can be the foundation, or it can be an extension.
One example I see often in Dataverse is intake processes. This work may not live anywhere else. It may not belong in an ERP, CRM, IT service-management platform, or another specialized product.
It may be unique to how the organization operates: how it receives an idea, request, opportunity, concern, exception, or assessment; how it decides what matters; who needs to review it; what evidence is required; and what happens next.
That is where Dataverse can become the foundation. It gives the organization a governed place to create the record, assign ownership, move it through a lifecycle, apply its rules, capture decisions, and understand the current state of the work.
Change Requests
Consider an organizational change request.
A team may identify a policy that no longer works, a handoff that creates delay, a control that creates unnecessary friction, a role that is unclear, or a customer-facing process that needs to change.
That is not necessarily an IT service request. It is not automatically a project task. And it may not belong in an ERP, CRM, or another specialist product.
It is a request to change how the organization operates.
The request needs to be understood and assessed. What is changing? Why does it matter? Who is affected? What processes, systems, controls, roles, or teams will be impacted? What risks does the change introduce? Who has the authority to approve it? What needs to happen before the change can take effect?
Dataverse can own that record from submission through impact assessment, review, decision, implementation, and outcome. It gives the organization one governed place to see the proposed change, accountable owner, affected stakeholders, supporting evidence, approval history, actions, and final result.
That is not a shadow ITSM process. It is the organization governing changes to the way it operates.
Work Dataverse Can Own Within a Larger Process
So what does that kind of work look like in practice?
It is usually not a single task, a static document, or a piece of information someone wants to keep nearby.
It is shared operational work. It has records that relate to one another, people with different responsibilities, and a lifecycle the business needs to trust. It includes decisions, actions, exceptions, and outcomes that matter beyond one person or one team.
Dataverse is often a strong fit when that work needs a durable, governed home and does not already belong to a product designed to run the domain.
That distinction matters.
The goal is not to move work into Dataverse because we can. The goal is to put operational work where the organization can secure it, automate it, reuse it, report on it, and maintain it over time.
Invoice Preprocessing
One example I have worked with is invoice preprocessing.
I would not advocate rebuilding accounts payable in Dataverse. The ERP should own the vendor invoice, the financial transaction, the accounting controls, and the eventual payment. That is what ERPs are built to do.
But there may be work that needs to happen before an invoice enters the ERP.
Dataverse is well positioned to handle that work. The invoice may need to be reviewed. Information may need to be validated. An exception may need to be resolved. More than one person may need to examine it, and it may need to move through a series of approvals before finance is ready to accept it.
That is not a replacement for the ERP. It is the governed operational process around it.
If we look back at the signals, this is where they become easier to see:
Signal
How your invoice process demonstrates it
Meaningful relationships
An invoice relates to a supplier, requester, cost center, purchase order or business context, approval requests, approvers, and eventually an ERP record
Lifecycle
Received → reviewed → routed → pending approval → approved or rejected → submitted to ERP → processed or exception
Different permissions
An AP processor, budget owner, approver, finance reviewer, and platform administrator do not need identical access
Dependable automation
The process needs routing, approval notifications, reminders, outcome handling, ERP submission, and error notification
Auditability
Finance needs to understand what was submitted, who approved it, when approval occurred, and why it was rejected or escalated
Data reuse
The invoice may appear in a model-driven app, approval experience, flow, finance report, exception queue, and ERP integration
Operational decisions
A person decides whether the invoice is valid, whether an exception needs attention, and whether funds or payment should be approved
That is the pattern when Dataverse owns the work. The harder discipline is knowing when it should not.
Work That Should Stay Elsewhere
My desire to make Dataverse the backend technology for the system of all systems began with some naivete.
Trust me, it was humbling to learn the limitations of Dataverse. But those limitations are worth noting.
Not because they make Dataverse less valuable, but because they force us to be more deliberate about what it should own and what should stay elsewhere.
Dataverse can store relational data. It can secure records. It can power applications, automation, reporting, integrations, and increasingly AI-enabled experiences.
But it is not a replacement for every system, every workload, or every collaboration pattern. It is not an ERP. It is not a document-management system. It is not an IT service-management platform. It is not a warehouse or lakehouse. It is not the best place for every note, task, file, transaction, or historical record.
The mistake is not using Dataverse. The mistake is treating capability as a reason for ownership. Just because Dataverse can store a record does not mean Dataverse should become the system that owns it.
Core Business Transactions
ERP has its place at the system of record for a reason.
An ERP is not simply a database with forms and approvals layered on top. It is built to run the organization’s core business transactions.
The general ledger. Purchase orders. Vendor invoices. Payments. Inventory movements. Customer orders. Billing. Fixed assets.
These are not just records with fields and statuses. They are transactions governed by accounting rules, financial controls, procurement policy, tax treatment, inventory costing, budget commitments, period close, audit requirements, and years of embedded business logic.
Rebuilding that behavior in Dataverse because Dataverse can model tables, relationships, workflows, and approvals is not innovation. It is taking responsibility for a domain the ERP was built to run.
Dataverse can still create real value around those transactions. It can own intake, preprocessing, exception handling, business-specific review, cross-functional approvals, tailored experiences, and automation. But the ERP should remain authoritative for the transaction itself.
Dataverse can coordinate and extend the work around the transaction. The ERP runs the transaction.
Specialized Operational Domains
The next category is specialized operational work.
Some work is not generic enough to rebuild from tables, forms, and flows without giving up the capabilities that make the specialist product valuable.
A service-management platform is not simply a ticket table. A project-management platform is not simply a task table. A contract-lifecycle-management system is not simply a document plus approval. A field-service platform is not simply appointments, work orders, and assets.
These platforms embody years of domain knowledge: service-level management, queues, change control, configuration relationships, resource scheduling, project dependencies, contract clauses, dispatch, maintenance plans, inspections, and controls specific to each industry.
Dataverse can integrate with these systems. It can create a governed process around them. But it should not become a shadow replacement just because a team wants one more app.
Work that should remain in a specialized platform
Why it belongs there
Appropriate Dataverse role
IT incidents, changes, problems, service requests, CMDB
ITSM platforms manage service levels, ticket lifecycles, change control, configuration items, and operational support
Business-demand intake, governance review, executive reporting, or cross-functional exception process
CLM products specialize in contract language, workflows, redlining, signing, and legal records
Intake, related initiative or vendor context, downstream operational obligations
Field dispatch, route optimization, maintenance scheduling, technician mobility
Field-service and asset-management products handle work orders, dispatch, service history, inventory, and maintenance
Supplemental inspection, custom governance, exception workflows, and business reporting
Security operations, alerts, investigation, and incident response
Security platforms are designed for telemetry, detection, correlation, response, and evidentiary investigation
Risk or compliance reporting around the security process, where appropriate
Large Scale Analytics and History
Dataverse should not become the organization’s default data lake, warehouse, or analytical archive.
A current operational record may belong in Dataverse. But years of transaction history, telemetry, application logs, clickstream behavior, IoT data, machine data, event streams, and enterprise-wide reporting workloads are different kinds of work.
They need ingestion pipelines, transformation, partitioning, broad historical retention, analytical engines, scalable compute, data science tools, and access patterns built for questions across millions or billions of records.
That is where Fabric, a lakehouse, a data warehouse, Azure Data Explorer, Databricks, or another analytical architecture belongs. Dataverse can remain the place where a user acts on a current operational issue. The analytical platform can be the place where the organization detects the pattern that revealed the issue.
High Scale Digital Products and Event Workloads
Finally, Dataverse is not automatically the backend for a high-scale digital product.
A public consumer application, marketplace, high traffic website, mobile product, telemetry platform, or real time event processing system may need global distribution, high throughput, low latency APIs, custom caching, event streaming, specialized search, independent deployment, and direct control over the application stack.
Those needs may point to Azure SQL, Cosmos DB, PostgreSQL, Redis, Azure Functions, Event Hubs, Service Bus, API Management, or a custom cloud architecture.
Dataverse may still participate. It may hold governed business records, customer-service context, internal operational workflows, or application-administration processes. But it should not be selected simply because it is the Microsoft data platform most familiar to the team.
A Power Pages site can absolutely serve a serious external audience. High traffic alone does not disqualify Dataverse or Power Pages.
The question is what kind of work the site is doing. Power Pages is a strong fit when the website is the front door to a governed business process: a partner request, application, case, inspection, onboarding process, or other Dataverse-owned operational record.
But when the digital product itself becomes the workload – when it depends on extreme transaction volume, real-time interaction, high-frequency events, specialized search, global-scale performance, or deep control over the application stack – a custom cloud architecture may be the better foundation.
Dataverse may still play a role. It may hold governed records, internal administration, exceptions, or human-review workflows. But it does not need to carry every request, event, click, or transaction just because the application is built on Microsoft technology.
Lightweight Work
And then there is work that should stay lightweight.
Not every early idea, personal task, team note, workshop output, or draft document needs lifecycle controls, security roles, relationships, integration, or audit history. Those tools are still valuable. They simply solve a different problem.
Dataverse Can Own, Extend, or Participate
By now, the question should not be, “Should this live in Dataverse?”
The better question is, “What role should Dataverse play in this process?”
There are three useful answers. Dataverse can own the work. It can extend the work. Or it can participate without becoming the system of record.
Dataverse role
What it means
Example
Own
Dataverse is the authoritative operational record from intake through decision, action, outcome, and closure
An organization-specific improvement request, policy exception, solution-readiness review, compliance attestation, or custom inspection
Extend
Another system remains authoritative for its core domain; Dataverse owns a distinct workflow around, before, or across that system
Invoice preprocessing and approvals before ERP payment; ERP exception management; cross-functional approval and review
Participate
Another platform owns the record; Dataverse contributes selected records, workflows, apps, reporting, or integrations without creating a duplicate source of truth
A Power App reads ERP data; Dataverse tracks a related business decision; an integration surfaces approved information elsewhere
The unhealthy answer is duplication.
If the ERP owns the invoice, do not create another competing invoice record in Dataverse. If the project-management product owns the schedule, do not create a shadow schedule in Dataverse. If SharePoint owns the document, do not create a second document repository. If an ITSM platform owns the incident, do not create another ticket queue.
The architecture becomes durable when each platform has a clear job, each critical record has a clear owner, and the organization can explain the boundary in one sentence.
A useful architecture test is this: Can we explain, in one sentence, what each system owns?
If we cannot, we are likely creating duplicate records, conflicting processes, unclear accountability, and a future debate about which system is the source of truth.
In the invoice example, the explanation is simple: Dataverse owns the review, exception, and approval workflow before the ERP transaction; the ERP owns the vendor invoice, financial posting, and payment.
That is a healthy boundary.
Dataverse does not need to be the database for every application or the backend for every system.
It is most valuable when it gives governed operational work a durable home: work with shared ownership, relationships, lifecycle, security, automation, accountability, and a need to connect people and systems.
Sometimes it owns that work directly. Sometimes it extends the system that owns the core transaction. Sometimes it participates without owning the record at all.
The point is not to maximize the amount of data stored in Dataverse. The point is to create an architecture in which important work has a clear owner, a clear lifecycle, and a trusted source of truth.
Bridge to Part 4
We will close out this Dataverse series in Part 4.
By then, we will have established that Dataverse does not need to own every kind of work. But when Dataverse does own the governed operational record, another question remains: where should people experience and collaborate around that information?
A source of truth is not defined by where data is visible. It is defined by where the organization governs, maintains, and trusts that data.
In the final part of this series, I will explore what it looks like to keep Dataverse as that governed source of truth while surfacing selected, approved information in Notion…giving people a more flexible work experience without turning Notion into an uncontrolled replacement for the system of record.
I don’t want to be the bearer of bad news—well, maybe just a little. My goal is to save you from yourself, so let me be clear: WE DO NOT CREATE UNMANAGED LAYERS IN PRODUCTION!
If you’re serious about maturing on the Power Platform, there are a few fundamental principles you need to grasp. I get it—the way Power Platform is set up, with everyone having a default environment, can feel like the wild west. But there are some non-negotiables. At the very least, you should be working with two environments—ideally, three: one for development, one for testing, and one for production-ready solutions. You know, a proper application lifecycle management (ALM) process—the same concept they drilled into us in college.
In theory—and in practice, based on what I’ve seen over the years (including my own mistakes)—you can do whatever you want. But at some point, you need to stop, take a step back, and ask yourself: Is a “hot fix” in production really worth the risk?
My answer? Absolutely not. And here’s why.
Governance
Yes, the G word—governance. Autonomy is great—until there’s a data breach. Then suddenly, we’re all asking how our favorite retailer leaked our PII (Personally Identifiable Information), and I’m staring at an email telling me to cancel my credit card, pack up, and start a new life.
Okay, maybe this article isn’t that dramatic—but it could be. Governance isn’t just a Power Platform concern; it applies everywhere. And while we champion secure backends like Dataverse, security only works if we take governance seriously. So, do yourself a favor—treat it like it matters. Because it does.
I have the privilege of working alongside some of the world’s premier experts in governance and compliance, partnering with some of the biggest names in business. And time and time again, we find organizations falling into one of two categories:
The Gentleman’s Approach
These organizations recognize the power of Power Platform but haven’t fully deployed it across their enterprise. They want to get ahead of the chaos by implementing governance before things spiral out of control.
Please Save Us
On the other hand, some organizations have already deployed Power Platform—without guardrails. Now, it’s out of hand, and they’re desperately looking for a way to regain control before things get worse.
If you’ve taken the Gentleman’s Approach, thank you. It makes life so much easier when we can focus on methodology and toolkits to set you up for long-term success.
If you’re in the Please Save Us camp, thank you as well. Acknowledging the problem and committing to steering your organization in the right direction is a crucial step.
The Problem with Poor Implementations
Here’s the thing—lack of governance in Power Platform leads to poor adoption. We have this powerful platform at our fingertips, but if security gaps, broken processes, or unchecked risks get in the way, no one wants to use it—or worse, no one can use it safely.
CIOs and the Tough Questions
Do CIOs make my job harder when it comes to governance? Yes. And they should. The tough questions need to be asked. Don’t just take my word for it—press me, challenge me, ask for sources. This is your organization, your investment, your future.
As a consultant, I care deeply about the services I provide, but at the end of the day, I will roll off your project at some point. My goal? To leave your organization better than I found it. And that is part of the reason we don’t create unmanaged layers in production!
The reasons that we’re here
Lack of Version Control
It’s right there, staring you in the face. You can do it. Just pop that flow open, add that missing action, and everything is right with the world—until it’s not.
Whatever the reason, you must operate with the mindset that tweaking production directly is not acceptable. You cannot predict unexpected behavior, and doing so completely undermines your version control and environment strategy.
If you have a production environment (and please don’t tell me it’s the default environment—that’s a conversation for another day) and some form of environment strategy, respect it.
Make your changes in the lower environments, test them, get sign-off, and deploy to production—the way it should be done.
Deployment Instability
In production, or at least in managed solutions within production, we have what are called solution layers. (I even included a nice, shiny image above to illustrate this—you’re welcome.)
When you create unmanaged layers, you introduce the possibility of deployment failures down the line. The artifacts and components in future deployments weren’t designed to handle the unexpected behaviors you introduced by manipulating a solution directly in production.
Save your deployments. Do it the right way.
Stick to the process—develop, test, get approval, deploy. Your future self (and your team) will thank you.
Difficult to troubleshoot issues
The degree of difficulty in troubleshooting can vary—but it becomes exponentially harder when you don’t even know what you’re looking for.
The unpredictability of removing a single component, a line of code, or an action in a flow can drive even the most rational person up a wall. One small tweak can have ripple effects you didn’t anticipate, turning a simple fix into a time-consuming nightmare.
Let’s avoid unnecessary cleanup, frustration, and sweat-inducing emergencies. Follow best practices, respect your environments, and don’t create an unmanaged layer in production.
Regardless of which camp you fall into, governance matters—whether you’re planning ahead or trying to clean up the mess.
Deploy managed solutions
This should go without saying—but I’ve seen it all. I’ve been on engagements where entire production environments were packed with unmanaged solutions, and it took months to convert everything to managed.
I want my clients to be set up for success, even if it means being a thorn in someone’s side. I get that introducing complexity, governance, and strategy might seem like extra overhead for IT. But trust me—it’s worth it.
Now, think about the alternative: years of unmanaged layers, uncontrolled deployments, and an unrestricted environment that you still call production. The overhead and nightmares that come with that far outweigh the effort of doing things the right way from the start.
If your goal is to build a mature Power Platform model, then you need a strategy in place. Create the necessary environments, security groups, and pipelines to establish a structured, secure, and scalable framework.
Call to Action
It All Starts with Education
Educate your business units, educate IT, heck—educate your dog if you have to. A mature Power Platform adoption is an organizational effort, not just an IT initiative. Do it the right way.
Use the Right Tools
You have plenty of tools at your disposal:
✅ The CoE Starter Kit – Provides a holistic view of your environment.
✅ Power Platform Admin Center – If you’re not ready for the CoE, at least establish a management strategy here.
✅ Security Groups (M365 or Entra ID) – Set them up early to enforce governance and access control.
One Rule Above All
Whatever you do, do NOT create unmanaged layers in production.
One would think that by now, we’d all be settled on what Power Automate can do. But surprisingly, that’s not the case. It feels strange to still use the word “potential” when talking about Power Automate, especially since I’ve seen it achieve some pretty amazing feats. It has served as an integration point between the Power Platform and external systems, connected Azure and the Power Platform, and even acted as the logic layer for Power Apps. Yet, I still find myself in conversations with customers, exploring the true depth of its power and capabilities.
Over the next few weeks, I’ll be sharing more about what Power Automate can do. This week we’ll be talking about Power Automate as the logic layer for Power Apps. Follow along to learn more!
The Logic Engine Behind Power Apps
Power Apps is very capable. The problem is that the more complex your logic gets, the more complicated your formulas become. In my experience as a Power Platform developer, it’s rare to build a solution without involving at least one Power Automate flow. These flows are usually triggered by something happening in Power Apps—either directly when the app triggers an automation or once a record that was changed in the app meets certain conditions (like a CRUD operation in Dataverse, SQL Server, or SharePoint).
Solution
Below is a refined version of your text, maintaining a casual style and clear flow:
Our solution will include two components: Power Apps and Power Automate. We’ll be working on a “Project Proposal” process, where every proposal requires approval. Depending on factors like scope or budget, the proposal may need multiple approvers or just one. I won’t walk through the entire build here since this post is purely informational, but I will share screenshots and add details to illustrate each step.
Canvas App
Project Proposal Main Grid
Project Proposals Grid
When you first open the app, you’ll see a main grid listing all project proposals. Each row shows basic details like project name, budget, primary approver, and scope. This screen lets you quickly scan and select any proposal for more information.
Project Proposal Entry Form
Project Proposal Entry FormProject Proposal Entry Form
This is where you enter all the core information for a new project proposal. It includes fields for basic details like project name, estimated budget, and scope. Once you fill out the form and submit, the proposal is added to Dataverse and triggers the Power Automate flow to handle scope logic.
Power Automate
Handling Scope Logic
Logic Handler
The crux of this solution is how we’re handling our logic. Sure, I could have written an expression with multiple If statements—maybe even nested ones—to get the result I wanted. But instead, I’m passing the necessary parameters to the Flow and letting Power Automate handle the conditional logic. If I ever want to add more conditions, I can do it in a visual way without diving into Power Fx.
Power Automate also offers other logic controls beyond simple If conditions, such as switch statements and parallel branches. This gives you a drag-and-drop interface to manage complex pathways—like sending notifications to different groups or looping through multiple records—without having to manually write or manage code. It’s all about making your logic more transparent and maintainable, so you can expand on it as requirements evolve.
Recently, someone asked me what I do for a living. My standard response is, “I am a Microsoft consultant.” I phrase it this way because the scope of Microsoft’s ecosystem is truly vast. Before I started consulting organizations on tools like Azure and Power Platform, I didn’t fully appreciate just how expansive Microsoft’s solutions really are.
The response I got was a little different than what I usually hear from business users, and I believe it was shaped as much by geography as by the products themselves. Milwaukee, WI, where I’m based, is a city historically known for heavy manufacturing, motorcycles, and beer.
This industrial legacy extends across Wisconsin, which has long been rooted in factories and industries, often relying on ERPs like Epicor or SAP. While Microsoft products are widely used here, they’re often limited to familiar tools like Word, Excel, and Outlook. However, Microsoft is far more than Word—it’s a robust platform that includes Azure cloud services, enterprise solutions like Dynamics F&O and Business Central, and the Power Platform. These tools empower both pro developers and low-code creators to build innovative solutions that drive business transformation.
Now, with the introduction of generative AI and Copilot, the Microsoft ecosystem has expanded even further, unlocking new possibilities for productivity and automation.
For those who still know Microsoft primarily for Word, I’m continuing a mission I started earlier this year: raising awareness of the incredible features included with Microsoft 365 licensing. Today, my focus is on the Business Standard license, specifically how it enables rapid development of custom business applications using SharePoint and Microsoft List templates. Let’s dive in and explore how you can make the most of these tools!
SharePoint/Microsoft Lists
One of the key benefits of a Business Standard license is access to SharePoint. SharePoint is an incredibly powerful tool, offering exceptional value for its cost. It serves as a collaborative workspace with features like built-in document management, intranet capabilities, and the ability to host and manage data in lists.
One of the key benefits of a Microsoft 365 Business Standard license is access to SharePoint, a robust tool offering exceptional value. SharePoint serves as a collaborative workspace with features like built-in document management, intranet capabilities, and the ability to host and manage data in lists.
While we won’t delve into all of SharePoint’s functionalities, let’s focus on its versatile lists feature. These lists are powerful tools for organizing and managing data, and you don’t need to access SharePoint directly to use them. The Microsoft Lists application provides an intuitive interface to work with these lists, making them a convenient and powerful data source for your business needs.
A significant advantage of Microsoft Lists is its accessibility across multiple platforms. You can manage your lists on the go using the Microsoft Lists mobile app, available for both iOS and Android devices. Additionally, while there isn’t a dedicated desktop app for macOS, you can use Microsoft Lists as a Progressive Web App (PWA) on your Mac. This setup allows you to work with your lists directly from your desktop, providing a seamless experience across devices.
This cross-platform availability ensures that you can access and manage your data whenever and wherever you need it, enhancing productivity and collaboration within your organization.
Solution
The template we’ll focus on today is the Asset Management template. While there are many templates to choose from, this one offers a great starting point for tracking and managing assets. It’s important to remember that you’re not limited to the fields provided in the template. SharePoint lists are customizable, and adding a new field to tailor the template to your specific needs is relatively easy.
If the out-of-the-box template meets most of your requirements but falls short in one or two areas, simply modify it by adding the necessary fields. This flexibility allows you to make the template truly your own while saving time and effort on development.
Available Templates
For posterity these are the templates available in SharePoint/Microsoft Lists:
1. Issue tracker
2. Employee onboarding
3. Event itinerary
4. Asset manager
5. Recruitment tracker
6. Travel requests
7. Travel requests with approvals
8. Work progress tracker
9. Content scheduler
10. Content scheduler with approvals
11. Playlist
12. Gift ideas
13. Expense tracker
14. Recipe tracker
15. Reading list
16. Apartment hunting
17. Job application tracker
18. Product support metrics
Step 1: Create list from template
In this step, we’ll select the Asset manager template. This will guide you through a wizard-like experience. On the next screen, you’ll see a preview of how the list will look when populated with data. From there, you’ll name your list and choose the option to create it. In just a few seconds, you’ll have a functioning data source for your asset management process.
Selecting templateExampleRenaming and creating templateEmpty list in your SharePoint site.
Step 2: Be creative!!
The next steps are entirely up to you. There are countless stories waiting to be told through data. As a business application developer, I tell the story of business data using tools like Power Apps and Power Automate. A data or business intelligence analyst might choose to use Power BI to craft insightful reports from the same data. The key takeaway here is that we now have a central repository for our data—one that was created quickly, can be easily iterated upon, and serves as a foundation for exploring what’s possible with Microsoft 365 licensing.
If you’re a CIO or business user, this approach can help you realize the potential of the Power Platform. It’s perfect for spinning up a quick proof of concept (POC) to demonstrate not just the art of the possible, but the art of what is. And what is—that Microsoft is far more than just Word. It’s an expansive, powerful platform ready to transform the way you work.
Extra
Next, I’ll share images of an application and flow I built using this list as the foundation. By starting with the Asset Manager template, I was able to significantly reduce the time spent planning out the architecture and get straight to work. This allowed me to focus on the “extras”—how I wanted the app to look and how I wanted to structure my flows.
In total, it took me about three hours to build and iterate on the process, refining my ideas about how everything should function. By integrating this data source with the Power Platform, users can rapidly develop solutions tailored to meet specific needs, enabling faster delivery and greater flexibility.
ListHome screen of mobile app for managing assetsFlow used to implement ‘sumIndex’ functionality in another list, simplifying sum retrieval in the app by offloading the logic to the flow, reducing the app’s processing load.
I want to start by saying that my recent work with Power Pages has been a significant learning experience. While I had some idea of what I was getting into, I had to quickly upskill. Here’s the key takeaway: don’t fear what you don’t know. Fear of the unknown often holds us back, especially in the tech world, where there are always countless uncertainties. We use tools to build solutions and inevitably face gaps or unknowns along the way. Instead of shying away, embrace those unknowns, expect them, and take action. Soapbox moment over.
Shoutout to Keegan
I have to acknowledge my colleague, Keegan, for this. We worked on a Power Pages project with SharePoint integration and want to ensure that only external users can view external-facing documents. Internally, a reviewer will upload documents to the external user’s record. While the internal reviewer will have access to all documents in the library, the external user will be restricted to documents stored in an external-facing SharePoint folder.
We experimented with several approaches to implement the segregation, but nothing seemed to work. Initially, we considered adding a metadata field to the document library to flag files or folders as external, then using JavaScript in the portal to filter out the results. However, I was concerned that if the JavaScript failed or loaded slowly, it could inadvertently expose sensitive files to users who shouldn’t have access. That potential risk made me uncomfortable with relying on this approach.
We noticed there had to be an internal process Microsoft uses to decide which folder gets surfaced in the subgrid. It was Keegan who suggested that maybe the ‘Created On’ field in the Document Locations Dataverse table was being used to determine which folder gets displayed. And sure enough, that was the case. So, big shoutout to Keegan for that insight. He even suggested I write an article about it, but I have no problem giving credit where it’s due—he’s a great guy.
Solution
I won’t be covering the process of setting up SharePoint integration with Power Pages in this article, but I will go over the basics of our table permissions. Anything beyond that is outside the scope of this discussion. If you’re unfamiliar with the SharePoint integration setup, please visit Manage SharePoint documents. Once you’re comfortable with that, return to this article and pick up the instructions from there.
Step 1: Configuring the Basic Form
For our solution, we didn’t need to use the entire Dataverse form; we just needed a simple space to upload files. You can choose to use the full form or modify it to fit your needs. We’ll be uploading our forms to a table we named “Site.” A quick note: avoid naming a table in your Dataverse environment “Site.” Power Pages uses a table called “Site” that holds the site record for the Power Pages website, so using the same name could cause conflicts within the environment.
Whatever permissions you set on the form; I recommend changing the parent permission to “Global” for the purposes of this discussion. You can adjust the permissions later based on your specific needs, but for now, this will help show functionality. Once you change the parent permission’s access type, we should be all set. Still, please note—until we complete the Power Automate flow, I suggest against uploading anything into the subgrid. You’d likely have to delete it later anyway.
Aside: Spoofing the System
One thing we noticed when manually walking through this process is that in the Document Location table in Dataverse, the first Document Location record “wins” and becomes the default. This is why we need to “spoof” the system a bit. When the first file or folder is added, it automatically creates the parent document location in Dataverse, which we don’t want in this case. Instead, we want to create the external document location first. Once that’s done, we create the parent document location. After the parent is created, we’ll associate it with the external document location. This ensures that the external location takes priority in the subgrid, making it visible only to external users on the Power Pages website, while files or folders outside of the external folder structure and including the external folder will still be visible to internal users via the model-driven app.
Step 2: Building the Flow
Building out the flow is fairly straightforward. We want to automatically create the folder structure every time a new site is created, ensuring that when documents are uploaded from Power Pages, they are always directed into the external-facing folder. To implement this design effectively, we’ll use an automated trigger.
1. Trigger: When a row is added, modified, or deleted
This trigger will monitor the creation of new sites and automatically build the necessary folder structure, making sure the external folder is ready for document uploads.
SharePoint Actions
To achieve the necessary design for our folder structure, we will be using two SharePoint actions. These actions will create both the parent folder and the external folder nested inside the parent folder. Both actions will utilize the “Create new folder” action.
2. Create new folder (Create Parent)
Site Address: This should be the location of your document integration. If you’re unsure of the SharePoint site location for the integration, you can review the document management settings in Advanced Settings to find it.
List or Library: Library where your documents are stored, check the site contents of the SharePoint site to verify the document library where you want your documents to be stored.
Folder Path: The folder path will be a concatenation of the Site Name and the Site GUID, separated by an underscore (_). This ensures a unique folder structure for each site created.
This setup will create the parent folder, setting the foundation for further customization with the external folder inside.
2. Create new folder (Create External Documents)
Site Address: Use the same site address as the Create Parent action.
List or Library: Select the same document library as the Create Parent action.
Folder Path: Using the dynamic content from the previous action, select the Title of the folder created in the parent action, then add a forward slash (/) and specify the name of your external documents folder (e.g., 0External_Documents).
This will nest the external folder inside the parent folder, ensuring that all external-facing documents are uploaded into the appropriate folder structure.
Dataverse Actions
Once the folder structure is set up in SharePoint, the next step is to establish the corresponding Document Location in Dataverse. Before proceeding with any Dataverse actions, navigate to make.powerapps.com and open the Document Locations table. Find and extract the GUID for the default site’s Document Location record, which should be titled “Documents on Default Site.” This GUID is crucial for the Dataverse actions you’re about to implement, so make sure the record is available and correctly identified before continuing.
1. Add a New Row (Create External Document Location Record):
Table Name: Document Locations
Name: External Documents
Parent Site or Location (Document Locations): sharepointdocumentlocations({GUID of Default Site record})
Regarding (Sites): dul_sites({GUID of the site in the trigger})
Relative URL 0External_Documents (Note: “Sites” is the table name being used for SharePoint integration in this example. Your table name may differ.)
It is essential to create the external document location record before proceeding with any further steps.
2. Add a New Row (Create Parent Document Location Record):
Table Name: Document Locations
Name: Trigger Name
Parent Site or Location (Document Locations): sharepointdocumentlocations({GUID of Default Site record})
Regarding (Sites): dul_sites({GUID of the site in the trigger})
Relative URL replace(concat({Name of Trigger},’_’,toUpper({GUID of Trigger})),’-‘,”) (Note: “Sites” is the table name being used for SharePoint integration in this example. Your table name may differ.)
3. Update a Row (Change Parent Site on External Folder)
Table Name: Document Locations
Row ID: {GUID of External Folder Document Location}
Parent Site or Location (Document Locations): sharepointdocumentlocations({GUID of Parent folder})
It’s crucial to update the parent site or location to align with the folder structure in SharePoint. This process leverages relative URLs, so it’s important that the relative URLs follow a consistent hierarchy. For example:
Default Site Relative URL: dul_site
Parent Relative URL: LinkedInTestSite
External Folder Relative URL: 0External_Folder
By correctly linking these records, the fully constructed URL will follow the pattern: dul_site/LinkedInTestSite/0External_Folder. This ensures the folder structure in Dataverse matches the structure in SharePoint, maintaining consistency and proper document organization.
Automated Dataverse trigger and creating folder structure in SharePointCreating external document location record in dataverseCreating parent folder document locationReparenting external document location with Parent record
In practice
Steps to Set Up the Internal Subgrid:
1. Open the main form for your table.
2. Add a subgrid to the form.
3. In the subgrid properties, select Related Records.
4. Choose Documents (Regarding) from the list.
5. Select All SharePoint Documents.
6. Save the changes.
7. Publish the form to apply the updates.
Test out internal subgrid
Navigate to the table with the integration and select a record.
Click Edit to open the form.
In the subgrid, choose the Document Location dropdown and select All Locations. This will display all files in the folder structure.
In my case, the internal subgrid shows 5 records, including those uploaded from Power Pages or residing in the External Documents folder, but on the Power Pages side, only 3 files are visible.
This is because documents on the Power Pages side are written to the External Folder, so only documents in that folder will be visible to external users.
Important: If you upload a file to any location other than the External Folder, it will not be accessible to external users.
If you select the External Documents tab in the internal subgrid and upload a file, it will be added directly to the External Folder, making it visible to external users.
Internal facing subrid with all files including files uploaded from Power PagesFiles residing in the External Documents folder shown here in Power Pages
I’ve just come back from a trip to Las Vegas, Nevada, USA, where I had the honor of attending the Power Platform Community Conference — it was fantastic. Before I continue, I must express how incredible the power of community is. My growth over the past six years has been significant, and I believe it has progressed in tandem with the Power Platform Community.
The inspiration for this post comes from the community. Professionally, I hold the position of Power Platform Supervisor, essentially an architect. Before reaching my current role, I was in the same position as many of you, trying to understand the intricacies of the Power Platform. There were a few trailblazers who had already started to unravel its mysteries years ago, like Matthew Devaney, Hardit Bhatia, Reza Dorrani, and Shane Young. Their pioneering work has been instrumental in my journey. Matthew even gifted me a bottle of Canadian maple syrup along with a touching note. This community is amazing!
Thank you “L”
During my time in Vegas, I encountered a kind young woman who, coincidentally, shares the same surname as one of my close friends, though they are not related. We’ll refer to her as L. Her humility and determination to resolve the issue with her app compelled me to offer some guidance.
I noticed many similarities between myself and L, particularly that she was using SharePoint as her data source, which is where many of us seasoned pros on the Power Platform started. Her dedication to her organization’s needs was clear. I sensed her passion, and on the first day, I offered her some advice that seemed to open up a new perspective on her app to her. The next day, she came back to the conference room, presented the progress she made overnight, and discussed her successes and challenges with me.
It’s incredible to think that nearly six years ago, I was in L’s position. I suspect that if her passion for the Power Platform endures, she’ll likely surpass me. That’s what it’s all about: spreading ideas and fostering a community that’s the antithesis of gatekeeping. If it’s a matter of money and career, there’s plenty to go around. Here, you’ll find no closed doors. L, I want to express my gratitude for reminding me of what it felt like six years ago and for giving me the opportunity to pay it forward. So, here’s to you, L – thank you.
Introduction
Canvas Apps are both fun and frustrating. I find the beauty to be in creating these pixel perfect custom applications, the angst in the patterns we must construct to make the app run as smoothly as possible. When done right, not only do you have a beautiful creation, but you also have beautifully written patterns that retrieve data efficiently and in an optimized manner that will make any “pro” developer proud.
Collections
Using collections is a method to enhance our application’s performance. Collections are, in essence, temporary tables or tables stored in the application’s memory, acting as a type of variable. Their strength lies in their capacity to store tabular data, which can be a precise replica of our data source.
This data can stay in the app until the collection is updated or destroyed, either by command or upon the application’s launch or closure. Storing data within the app significantly boosts performance, as it eliminates the need to repeatedly retrieve data, thus avoiding performance lags. When fields in your forms or galleries draw from the collection, updating them provides instant feedback to those fields, bypassing the need for a patch to the data source and the later retrieval of the updated value or reloading of the collection.
Moreover, collections value extends to improving the UI of our canvas app, enabling us to write conditional patterns in our formulas that adjust the visibility of controls, borders, colors, and similar elements.
Collection Construction
Filter Criteria
Before we continue with our exploration of collections, it’s important to consider certain aspects of how we will construct our collection. Although collections can be an exact mirror of our data source, it is crucial to contemplate the techniques we use to filter our data source.
Ensuring we have proper filter criteria is crucial because it prevents us from retrieving unnecessary data. We aim to avoid overloading our application with irrelevant rows that do not contribute to the operations we intend to execute. When considering data sources or datasets, think about the number of operations that can be derived from a single source. With this consideration, meticulously decide your objectives and set filter criteria that serve as a pathway to those goals. Otherwise, you end up creating collections that deviate from the original intent of your project.
Example 1: Filtering Data Source based on Status
A prevalent method for applying filter criteria is to set up the database to return only active records. The first example will show this approach.
Using my SharePoint List:
Create Collection called colBooks
Filter SharePoint List by Status field to retrieve records that are active
Display records in items property of table
Creating collection with no filter criteriaUnfiltered collection in data table controlUsing Filter function toretrieve records with Active statusFiltered collection
Example 2: ShowColumns()
Another way to create a collection in Power Apps is by using the ShowColumns() function. This function enables developers to explicitly choose which columns they want to include and make available within the app.
Our method for building collections improves performance by retrieving only the essential columns. This reduces the app’s load, resulting in faster execution, especially when working with large datasets.
In my experience, there’s rarely a valid reason to retrieve every column from a data source. While clients may sometimes ask for this, it’s crucial to remember that doing so can pull in unnecessary system columns. For example, in SharePoint, this will include columns like {ModerationComment} and {ModerationStatus}, among others.
Formula for ShowColumns(), explicitly selects the columns needed and leaves out the restExplicitly selected columns on view in table
Example 3: DropColumns()
The inverse of ShowColumns() is DropColumns(). While ShowColumns() lets you explicitly select which columns to show in the app, DropColumns() lets you specify which columns should be excluded. This is particularly useful when you need most of the data but want to omit specific columns, like unnecessary system fields or sensitive information, keeping the app more efficient and secure.
Streamline your app with DropColumns() by removing unnecessary fields and keeping only essential data.The ISBN field is empty because we used DropColumns() to remove it when refreshing our collection
Example 4: 1 Record Collection
What’s the point of having a collection with just one record? While collections are typically used to display tabular data, they’re also highly flexible and can be updated. I often pull a single record into a collection to isolate it and work with that specific data. In my opinion, it’s more efficient to modify a single record in a collection than to reset an entire variable.
To illustrate this with a use case: In Power Apps, the out-of-the-box form experience can be limited, and there are situations where creating a custom form is necessary. I often do this using a gallery, as the custom form only needs to handle one record at a time. In such cases, I create a single-record collection and assign it to the gallery’s Items property. This makes the contents of that collection available to the gallery or custom form, allowing for more flexibility and control in handling the record.
Using First() function to return the first record from the data source and create a single record collectionSingle record collectionUsing LookUp function to filter based on unique value (ISBN) to create single record collectionSingle record collection filtered by ISBN
Example 5: Real Time Feedback
A key advantage of a collection is that it stores tabular data in memory on the client side. Since the collection is available within the app, you can change it without needing to alter the data on the server. This allows any control using the collection as its data source to instantly show changes once the collection is updated, providing real-time flexibility and responsiveness in the app.
To update a collection, there are several techniques you can use depending on your needs. You can leverage functions like Update(), UpdateIf(), and Patch(), among others. Each of these offers a different approach—Update() replaces an entire record, UpdateIf() modifies records based on a condition, and Patch() lets you selectively update specific fields within a record. These options give flexibility in how you manipulate data within the collection, enabling efficient real-time updates.
Notice price before modificationFormula to update Price of last book in the collectionUpdated price
Conclusion
Once again, I’d like to thank my friend “L.” As a consultant who works with many clients throughout the year, it’s easy to forget those early days and the challenges you face when starting out as a Power Platform developer. If you’re like “L” and need some guidance, don’t hesitate to reach out to the community through various social media forums or the official Power Platform community. There’s a wealth of knowledge and support available.
Collections are a powerful tool, and I encourage all canvas app developers to take full advantage of them. Not only can collections help improve performance in terms of data retrieval, but they can also enhance the user interface and overall app functionality, as shown in the examples throughout this article. There are countless ways to leverage collections, so I urge you to explore their potential. Happy building!
Alright, Azure and Power Platform. It’s easy to overlook, but remember, Power Platform is cozily nestled right into Azure. I’ve gone from saying I build apps to now, more accurately, saying I work in the cloud.
Azure is essentially the backbone of the cloud world for Microsoft 365 and Power Platform, now Azure OpenAi. Between you and me, Azure has long had Ai capabilities with Azure Cognitive Services.
Azure has replaced the need for those loud, space-eating server rooms with a sleek, digital space. I remember the days of hiding in server rooms to escape the constant barrage of IT queries. Now, with Azure, those days are just a funny memory.
So, why Azure? Well, it’s vast. When my wife expressed interest in learning it, I had to ask, “Which part?” because it’s that big. It’s not just one thing; it’s a whole ecosystem waiting to be explored.
The cost might make some hesitant. I’m not claiming to be a Microsoft licensing expert, but generally, if the benefits are clear, people find the costs justifiable. And with Azure, the benefits are often worth it.
My journey and new role have led me deeper into Azure, especially Azure Functions. These have been a game-changer in my Power App development. They allow me to execute code, like JavaScript and my new favorite PowerShell, for complex tasks I’d rather not handle directly in the app. It’s been a great push to brush up on my coding skills too. I’ve reacquainted myself with Visual Studio Code as the last 4 years or so, I’ve lived in the Power Apps formula bar and Power Automate expressions.
But it’s not just about using Azure for the sake of it. Integrating Azure into your Power Platform solutions can significantly enhance what you’re able to do. It’s about making your solutions more robust and efficient. Azure Logic Apps, for instance, are like Power Automate on steroids, offering more capabilities and flexibility.
In essence, leveraging Azure within your Power Platform solutions isn’t just a good idea; it’s a strategic move. It’s about expanding your capabilities and delivering more sophisticated solutions. So, consider taking that step into Azure. It’s a solid move for anyone looking to enhance their Power Platform projects.
I recently had a conversation with a friend about the relevance of Access databases in today’s tech landscape. To my surprise, he emphatically affirmed that Access databases are still widely used. The last time I encountered Access was during my college days when it was used as a teaching tool for database modeling. Honestly, I had no idea that organizations continued to rely on it.
In my personal journey, Access has played a pivotal role in enhancing my skills with Power Automate Desktop. Initially, my goal was to create a Model Driven App and utilize it in conjunction with Power Apps desktop to execute desktop flows. This approach worked reasonably well, but over time, I no longer had the Model Driven App and didn’t want to recreate it. That’s when I turned to Access as my training ground.
However, even my practice with desktop flows on Access forms had its limitations. During my experimentation with PowerShell, I became determined to find a way to insert data into an Access database file without ever having to open Access itself. So, for those who still rely on Access in their workflows, This one is for you…
Here’s what you’ll need: a designated location to store your Excel file. Personally, I store mine in a folder on my desktop.
Install ImportExcel Module
To install the ImportExcel module, please open PowerShell and execute the provided script below:
To create an input variable for storing the path to the Access database we are working with, follow these steps to configure it:
Open the Power Automate Desktop workflow where you intend to use the variable.
Locate the area where you can define variables, typically in the workflow settings or variable panel.
Create a new variable and give it a meaningful name, such as “varAccessDb.”
Set the data type of this variable to “String” since it will store the file path, which is typically a text value.
Save your workflow to ensure the variable is retained.
Now, you have an input variable named “varAccessDb” that can be used within your PowerShell script to hold the path to the Access database.
Retrieving Excel File From Folder
To initiate the process, the first action we need to perform is to retrieve the Excel file(s) that will be used in our flow. Follow these steps:
In the Actions pane on the left-hand side of your Power Automate Desktop interface, type “Get files in folder” in the search bar.
Locate the “Get files in folder” action in the search results.
Drag and drop the “Get files in folder” action onto your workflow canvas.
This action will allow you to specify the folder from which you want to fetch the Excel files for further processing in your flow.
To direct the “Get files in folder” action to the folder containing your Excel workbooks and set it up to collect all files with a .xlsx extension, follow these steps:
Select the “Get files in folder” action block on your workflow canvas to access its configuration.
In the “Folder path” input field, specify the path to the folder where your Excel workbooks are located. You can either type the path or use the browse button (…) to navigate to and select the folder.
In the “File filter” input box, input “*.xlsx”. This filter will ensure that the action captures all files with the .xlsx extension.
Save your configuration.
Now, the “Get files in folder” action is configured to retrieve all Excel files with the .xlsx extension from the designated folder for further processing in your flow.
File filter: *.xlsx
Save.
Create “For each” loop
To make your solution dynamic and capable of handling one or multiple files, you can set up a “For Each” loop. Follow these steps:
In the Actions pane, search for “For Each” to find the For Each loop action.
Drag and drop the “For Each” loop action onto your workflow canvas.
In the “Values to iterate” field of the “For Each” loop, use the %Files% variable that was generated from your “Get files in folder” action.
To enhance readability, you can rename the “Store into” section from “CurrentItem” to “CurrentFile.”
By doing this, you’ve set up a loop that will iterate through each file retrieved by the “Get files in folder” action, allowing your workflow to handle both single and multiple files seamlessly.
Launch Excel
To add a “Launch Excel” action within your “For Each” loop and configure it, follow these steps:
In the Actions pane, search for “Launch Excel” to locate the “Launch Excel” action.
Drag and drop the “Launch Excel” action into your workflow editor, making sure it is nested within the “For Each” loop.
Now, let’s configure the “Launch Excel” action with the following parameters:
File path: This should point to the current Excel file being processed in the loop. Use the “CurrentFile” variable to specify the file path dynamically.
Visible: You can choose whether you want Excel to be visible during the execution of your workflow. Set it to “No” if you want Excel to run in the background without displaying the user interface, or “Yes” if you want Excel to be visible.
Create new instance: You can choose to create a new instance of Excel for each file, ensuring that each file is processed independently. Set it to “Yes” if you want a new instance for each file, or “No” if you want to reuse the same instance for all files.
Run macro (optional): If you have a specific Excel macro to run, you can specify it here. Otherwise, leave it blank if you don’t need to run a macro.
By configuring the “Launch Excel” action in this manner, you ensure that your workflow opens and processes each Excel file one by one within the “For Each” loop.
Launch Excel: and open the following document Document Path: %CurrentFile.FullName%
Certainly, if you prefer to make the Excel instance visible, you can set the “Visible” option to “Yes” in the “Launch Excel” action configuration. This choice will allow you to see the Excel interface while the workflow is running, which can be helpful for monitoring and debugging your automation process.
Read from Excel worksheet
To add a “Read from Excel worksheet” action and configure it as described, follow these steps:
In the Actions pane, search for “Read from Excel worksheet” and drag it into your workflow editor. Ensure that you place it within the same “For Each” loop as the “Launch Excel” action.
Configure the “Read from Excel worksheet” action with the following parameters:
Excel instance: %ExcelInstance% Retrieve: All available values from worksheet
Running PowerShell Script to insert data into our Access database
This PowerShell script will loop through the Excel data and insert records into the specified Access table without the need to have Access open. It should significantly improve the efficiency of your workflow compared to looping through each record in the spreadsheet using multiple actions in Power Automate Desktop.
Add a “Run PowerShell script” action to your workflow.
Copy and paste the following PowerShell script into the action, making any necessary updates to match your specific file paths, table names, and data variables:
# Define the path to the Access database
# varAccessDb is the our input variable
$accessDBPath = "%varAccessDb%"
# Define the connection string
$connectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=$accessDBPath;Persist Security Info=False;"
# Define the path to the Excel file
$excelFilePath = "%CurrentFile%"
# Import data from the Excel file
# Ensure that your Excel file has columns named PartName, SKU, Description, and Price
$dataCollection = Import-Excel -Path $excelFilePath
try {
# Create a connection object
$connection = New-Object System.Data.OleDb.OleDbConnection($connectionString)
# Open the connection
$connection.Open()
foreach ($item in $dataCollection) {
# Define the SQL query for inserting data
$query = "INSERT INTO Part ([Part Name], [SKU], [Part Description], [Price]) VALUES ('$($item.PartName)', '$($item.SKU)', '$($item.Description)', $($item.Price))"
# Create a command object
$command = New-Object System.Data.OleDb.OleDbCommand($query, $connection)
# Execute the command (perform the insert operation)
$command.ExecuteNonQuery()
}
# Close the connection
$connection.Close()
}
catch {
# Handle the error
Write-Host "An error occurred: $_"
}
finally {
# Ensure that the connection is closed even if an error occurs
if ($connection.State -eq [System.Data.ConnectionState]::Open) {
$connection.Close()
}
}
Welcome back for Part 2 of Solutioning. If you missed Part 1 please check it out.
In this week’s discussion on enhancing your Power Platform solutions, we’ll be delving into the process of incorporating existing tables into your solution. Before we dive into the details, it’s essential to recognize that there’s no mystical formula for adding a component to your solution. This reminds me of a valuable lesson imparted by one of my colleagues, especially when discussing governance: “Just because we can, doesn’t mean we should.” This principle directly applies to the inclusion of tables in a solution. Simply having the ability to add every field from a table into your solution doesn’t automatically mean it’s the best course of action. Let’s explore this further.
Business Value
In the context of Power Platform solutions, adding an existing object to a table may not seem immediately impactful, but it holds significant value for your business.
Here’s a brief story to help illustrate this:
To better understand the importance of adding only the necessary fields to your table within a Power Platform solution, let me share a personal experience that highlights this point. It relates to that insightful phrase from my colleague. Here’s what happened:
I was working on a Power Platform solution—whether it was an app or a flow—in a development environment, preparing it for the transition to the testing phase. In the early stages of building the solution, I faced a crucial decision when incorporating an existing table into the solution. At that moment, I chose to select the ‘Include all objects’ checkbox without giving it much thought.
Little did I know, I unintentionally created a situation that would require some effort to resolve. As we all understand, time is valuable in business.
In a development environment, it’s not always easy to track who’s working on what, and how changes to a table schema might affect your deployment. Communication isn’t always perfect. To reduce risks and minimize errors, it’s a good practice to take some precautions.
In simple terms, this approach helps to prevent errors, leading to quicker issue resolution. This saves time, money, and the stress of figuring out why you’re encountering errors related to missing dependencies that you didn’t directly modify.
Adding existing table to solution
Within our solution, follow these steps:
Select “Add Existing.”
Choose “Table” from the options.
Pick the specific table you wish to work with.
Then, click on “Next” to proceed.
5. Now, go ahead and choose “Select objects.”
At this point, you’ll be presented with a list of all the objects that are currently part of the selected table.
Pick only the objects that you plan to include in your solution.
Then, click the “Add” button to add the selected objects.
On this screen, you’ll notice a count of the objects you’ve selected.
Go ahead and click the “Add” button to proceed.
You will now see that you have added your table to the solution
To view the contents of your table, click on it. (Please note that if there is data in the table, you’ll be able to see all the columns and data. However, these columns are not automatically included in your solution when you move it.)
Now, choose “Columns,” and you will only see the column or object that you added to the solution.
Thank you for joining us this week. In our next installment, we will continue to explore the world of solutioning. Our ultimate aim in solutioning is to lead into a discussion about Application Lifecycle Management (ALM). So, stay tuned for more exciting insights!
