3 approaches to reshape data in Power Automate Efficiently
Earlier this week I was asked to reshape data in Power Automate. Having an array of data that needs to be reshaped just sot that an API can take the data can be tricky. So often I find clients using the Append to array action inside an apply to each. Now if your array only has a few records it probably doesn’t matter too much but what if you have 1000s of records?
The Data samples
Table of Contents
The sample input data that I was given looks like this:
The first approach, which is often used, uses the append to array action. If you have a small array this is probably ok.
However for large arrays this isn’t an efficient way of doing things, as the concurrency settings will not work inside apply to each steps when you use variables inside the apply to each.
The expressions used for phone and first name are:
The second approach, which is a bit better than the first approach is to use Pieter’s method.
Using the second approach means that we avoid any variables, which opens up the option to process the data faster when we enable to Concurrency/Parallelism in the settings of the Apply to each step.
The expressions used for phone and first name are:
The fastest of the three approaches is the select action. The select action is the best approach.
If you use the above Mapping mode where you specify each of the properties or the below text mode doesn’t really matter.
Putting the approaches to the test.
The expressions used for phone and first name are:
item()['phone']
item()['firstname']
If you are working with fields that may be empty you could also use the JSON query syntax
item()?['phone']
item()?['firstname']
Comparing the options
Before enabling the parallelism setting on we will find that processing 75 records takes about 13 seconds when the apply to each steps are involved. The select however takes no time at all to run.
When we enable the parallelism and set it to the maximum of 50 then we will see the real difference in the three approaches.
I have become quite spoiled by modern CRM platforms like Dynamics 365. On any given weekend, I will spend a few hours making enhancements to our Thesis Technologies CRM system. Although this might sound a bit administrative, this time is very strategic because it allows me to take ideas regarding our business process and implement them immediately. This is only possible because our Dynamics 365 CRM system is flexible and easy to change.
Why does this flexibility matter? Flexibility makes you money. When you can continuously shape and form your business processes in real time, you are continuously improving your business. When you continuously improve your business week after week after week, you eventually transform your business processes, your bottom line, and the engagement level of your staff.
I realize that you may not embrace the idea of learning to modify your CRM system and spending your weekend making changes yourself. I don’t blame you. There are other ways we help clients leverage this flexibility in their businesses. Sometimes the ThesisTech business consultants serve as the implementers of continuous change. We are on call for our clients and when an idea needs implementing, we make it happen as quickly as possible. We can also train people in your organization to customize your CRM. It doesn’t matter how you get it done, just make sure that you are able to implement great ideas as they come to light.
Why leverage the flexibility of your CRM? Here are 5 specific benefits to your business.
1. Implement New Ideas
Most business leaders are constantly thinking about ways to improve their business. This is because there seems to be endless opportunities for more sales and more profits. Ideas can be small like adding a new field to a report or ideas can be bigger like adding a new step to your sales process. When these new ideas occur to you, modern CRM systems enable you to implement them very quickly and realize the benefits in a day or a week instead of months later.
The most profound impact of this flexibility is to create an environment where new ideas are capitalized on. Way too often, business systems are set in stone, and we live with them for years or even decades. In the past, systems made us slaves to their functionality because of the cost of change. This is no longer the case. Dynamics 365 can respond to your ideas and put you in charge of your business improvement.
2. Respond to Business Changes
Another great reason for flexibility in business systems is the constantly changing environment we all operate in. New regulations, new work situations (like COVID), new business opportunities, or new competitive threats can all demand changes to our business processes. Sometimes these changes are temporary and other times they are permanent.
Flexible CRM systems allow businesses to react to these changes quickly and inexpensively. Being able to respond quickly in these situations puts you in a competitive advantage in your market and allows you to play offense in the face of change instead of constantly being on defense.
3. Correct Past Mistakes
Depending on the complexity of your sales process and the needs of your business, CRM implementations can be a handful sometimes. Even with a strong implementation partner, changes often need to be made to a CRM system after the initial startup phase. Sometimes users realize that more automation is needed to support the team. Other times users have even better ideas for the system once it has been implemented for a while.
Maybe the word “mistake” is a bit harsh in this context because often we don’t know what we don’t know. But the good news is, when you have a flexible CRM system, changing direction or enhancing current functionality can be done very quickly. In fact, there is great value in quickly implementing initial functionality so that feedback and hands-on experience can inform future changes to the system (see more notes on an “agile mindset” below).
4. Take Advantage of New Technologies
In this cloud-first business systems environment, CRM systems are constantly being enhanced and super-charged by very capable companies like Microsoft. In fact, you often don’t need to do anything to suddenly have access to powerful new technology. One of my favorite examples of this has been the evolution of the global search function in Dynamics 365 CRM. What started out as a very ho-hum search feature is now a very impressive tool in finding what you need in your CRM. To take advantage of this enhancement, users had to do nothing. It just showed up with a new release. Other technology advancements may require some CRM work to take full advantage.
For example, another great advancement in Dynamics 365 is the integration between the CRM and Microsoft Teams. This can be a game changer because it enables new processes that eliminate redundant work and help team members work together better. Enabling this functionality just requires a couple of settings changes and a little training. When you have the right CRM system, you are leveraging new technology that can create strong advantages for your business.
5. Practice Your Agile Mindset
The more you realize the benefits of leveraging the flexibility of your CRM system, the more you will want to do it. It just takes a little practice! Many of us are in the mindset that computer systems offer a fixed set of functionalities and are very difficult to change. This is true of systems in the past, but it is not true of modern CRM systems. We need to develop an agile mindset where change and improvement are our normal course of business. Try it, change it, improve it. Move your business forward through a cadence of constant business process forming and reforming. If your business systems do not support this mindset, then maybe it is time to take another look at what is available today in the business systems arena.
Dynamics 365 Sales is an enterprise level CRM system and Microsoft Teams is an industry leading communication and collaboration tool. You are most likely familiar with both cloud-based products offered by Microsoft. In this post, I would like to describe a powerful integration that is available when you use these two products together.
In recent years, Microsoft has adopted a strategy of eliminating “context switching” or the need for a user to jump to another application to get the information needed to do their job. This strategy is achieved in a powerful way by allowing organizations to embed Dynamics 365 records inside of a Teams channel without any additional licensing.
Here is one use case in particular worth noting.
Use Case: Embed an Opportunity Record in Teams
Opportunity records in a CRM system often contain the most important information. For many companies, CRM opportunities are the jobs, projects, or products that bring in the revenue. Modern CRM systems allow complete customization of the fields and forms related to opportunity records.
In the case of ThesisTech, our CRM opportunities represent our sold and unsold projects related to the business software consulting services we provide. Our opportunity records are highly customized and contain all the information required for any given project including project scope, client overview, project approach, specifications, files, WBS (work breakdown structure), hours, invoices, client emails, and more.
We share all this critical information with our project teams by embedding the Dynamics 365 CRM opportunity record form inside a Teams channel as a new tab. This connection is created through a few quick clicks in Teams or in Dynamics 365.
Once connected, each team member can see all tabs and data related to the project. The type of access granted to each project team member reflects their security role in the organization as it relates to this project.
Business Benefits Due to This Integration:
1. Improve Team Efficiency
Project team efficiency is improved significantly by creating a single location for all project related information. The Dynamics 365 CRM record inside of Microsoft Teams puts this information at the fingertips of each team member in a well-organized manner.
The need for accessing different locations for key information and documents goes away. The need to constantly communicate key project information to team members via email or other means is virtually eliminated.
2. Improve Team Effectiveness
When all project information is found in a single location, the collaboration capabilities of Microsoft Teams become even more effective. Everyone sees the same data and this data is “live”.
Project teams are more effective when they are on the same page during each phase of the project. The quality and timeliness of project delivery also goes way up when team members have full access to all important project information.
3. Unlimited Tabs of Relevant Information
The Dynamics 365 CRM system is highly customizable which means the information that can be shared inside of Microsoft Teams is virtually limitless. How this information is organized and presented is up to you.
My favorite way of organizing project information in Dynamics 365 is to have a specific opportunity form tab for each subject. This approach makes things easy to find and avoids the clutter that often occurs in a CRM form with a bunch of unrelated information mashed together.
4. Use Security Roles to Secure Confidential Information
There are times when a project team does not need to see all information related to a project. For example, you may not want the financial details of a project available to everyone. This situation can be addressed by using Security Roles inside of Dynamics 365. Roles can be used to drive which tabs or fields appear based on the user’s role on the project.
5. No Additional Licenses Required
Another great benefit of the Dynamics 365 and Teams integration is that no additional licenses are required. Project team members who do not have a Dynamics 365 license can still see and use the information from Dynamics when it is embedded in Teams.
Aggregating Year-to-Date Data in Microsoft Dynamics 365
Microsoft Dynamics 365 provides a wide range of convenient tools for IT teams to deliver efficient and accurate reporting of business partner data. A common request, particularly in sales, involves displaying year-to-date values for various fields (revenue, costs, units sold). It seems simple enough. However, complications can arise when aggregating data across multiple entities in Microsoft Dynamics 365.
Aggregating Data for Year-to-Date Revenue by Account
A company has multiple Accounts, and each Account has Orders that are associated with it. The requirement is to display the total Year-to-Date Revenue on each Account. Each Order includes an Order Total, which represents the currency amount received. It also includes an Order Date, which indicates when the Order was placed. This new Year-to-Date Revenue field sums the Order Total values on Orders associated with an Account. Only Orders placed in the current calendar year are included.
Common Approaches to Aggregating Data and Their Limitations
We only need to build logic for one field. Ideally, this should not require complex development. However, as we investigate further, we find that it is not that simple.
Calculated/Formula Columns: Simple, but Limited Calculated columns are the easiest way to automatically compute data that relies on other fields. The column itself does the calculation and does not require any additional workflows/code. Another benefit is that the calculation is synchronous. As a result, the value stays up to date automatically. However, Calculated Columns cannot access data from multiple entities. Since we want the field on the Account to display the sum of a field from another table, this approach does not meet the requirement.
Rollup Columns: Powerful, but Not Year-Aware These are very similar to Calculated Columns. The main difference is that Rollup Columns can connect to other entities. This typically is the most efficient way to gather data for reporting. One drawback is that these fields are not synchronous. They update automatically on a 12-hour schedule, or they can be recalculated manually. This approach works only when real-time data is not required. Regardless, in this scenario, we are looking for Year-to-Date functionality. Rollup Column date field conditions can only filter on fixed date ranges and do not support a ‘This Year’ operator. Therefore, this also will not fulfill our need.
Design Considerations When Aggregating Data Across Entities
We have ruled out simple column computations, so we will need to look at workflows and code. All the options below can work for this task. However, you should consider the pros and cons of each approach when choosing the best solution.
Workflow and Code-Based Approaches for Aggregating Data
Power Automate: Flexible, but Costly at Scale Power Automate is an easy and user-friendly way to manipulate data across multiple entities and systems. Power Automate can handle this scenario. However, there are several limitations to consider. Orders can be created and updated at any time. As a result, the workflow would need to trigger on each event. Depending on system traffic, this workflow would run frequently. It would also recalculate the same data. Over time, this becomes inefficient. If real-time updates are not a concern, the flow can run on a schedule. Even then, unnecessary recalculations still occur. Furthermore, if we are dealing with a large number of Accounts and Orders, Power Automate can execute very slowly.
Plugins (C# Code): Scalable, but Developer-Dependent Plugin code runs similarly to Power Automate. However, plugins scale much better for high-volume transactions. This approach optimizes performance for our scenario. The downside to choosing this route is it requires more advanced developer knowledge to setup and maintain, as you will need to understand C# and/or similar coding syntax to build out this logic.
JavaScript: Fast for Forms, Limited Beyond Them If we want to avoid running a workflow or plugin every time an Order is created or updated, we can use JavaScript to obtain the required data. This will run any time the Account form is loaded and is a great way to handle computing a field that requires filters and relies on other entities. JavaScript will calculate the data to display on the form, but it will not save the value to the given table. Meaning, if that same data is needed in other areas (dashboards or forms), this is not an efficient way to grab data for long term use.
A More Efficient Way of Aggregating Data with FetchXML
Once you have chosen which option makes the most sense for your needs, it is now time to build out the logic. There are many ways to do this, but collecting and summing mass data can lead to unnecessary looping. The most streamlined way is to aggregate data through a FetchXML Query. FetchXML includes grouping and aggregation features that let you calculate sum, average, min, max, and count across multiple rows of data.
How FetchXML Reduces Looping and Improves Performance
This query uses the aggregate ‘sum’ function on the Order Total field, while filtering for those Orders that belong to the provided Account and fall within the date range we want. This single call provides everything we need. No looping is required. This approach lets the platform handle aggregation at the query level. It avoids iterating through individual records in workflows or custom code. As a result, it avoids iterating through individual records in workflows or custom code. From here, you can add additional conditions to meet the requirements. Some other suggestions would be to only look at active records or filter out Orders that are cancelled/returned.
Additional Supporting Microsoft Documentation
Key Takeaway: Optimize for Sustainability, Not Just Possibility
Aggregating data in Microsoft Dynamics 365, especially for year-to-date reporting across related entities, rarely comes down to what is possible, as we learned above there are many ways to perform these tasks. However, it is more so about what is efficient and sustainable for long term use. FetchXML calls via workflows or code offer a way to reduce system load and improve reliability when traditional column functions cannot handle the task.
Working with New Dynamic
New Dynamic is a Microsoft Solutions Partner focused on the Dynamics 365 Customer Engagement and Power Platforms. Our team of dedicated professionals strives to provide first-class experiences incorporating integrity, teamwork, and a relentless commitment to our client’s success.
Stop Automating Tasks. Start Automating Decisions: AI Agents in Dynamics 365
Are you building a system that only follows orders, or are you cultivating a digital workforce that actually thinks?
Let’s be honest: most "automation" in the last decade was just a faster way to do dumb things. We built scripts to move data from Field A to Field B, and we patted ourselves on the back. But in a market that moves this fast, has to be about more than just speed. It has to be about intelligence.
This is the shift that keeps me up at night—in a good way. We aren't asking "How do we automate tasks?" anymore. We’re asking "How do we automate outcomes?" This is where AI agents in Dynamics 365 stop being a buzzword and start being a competitive weapon.
Traditional automation handles rules. AI agents handle reasoning. When you embed these , you aren't just installing software; you're hiring a team that doesn't sleep and actively fixes problems before your human team even logs on.
Understanding AI Agents in Dynamics 365
To truly get this, we have to stop thinking of automation as a static script and start viewing it as a dynamic team member.
Here is what that actually looks like under the hood. At their core, these agents are autonomous. Unlike the brittle bots of the past that crashed the moment a variable changed, Microsoft Dynamics 365 AI is designed to roll with the punches.
They speak human: They interpret intent, not just syntax. You don't need to speak code to get them to work.
They find the signal: They digest unstructured mess—Excel sheets, email threads, PDFs—to find what matters.
They learn from the wins (and losses): They refine their accuracy based on historical success, effectively "learning on the job."
They make calls: They weigh specific circumstances rather than blindly following a "yes/no" tree.
These agents operate across Sales, Finance, and Supply Chain departments, bringing AI-driven business processes to the enterprise level. If you are struggling to picture how these agents would actually live within your current data ecosystem, We can show you exactly what an outcome-based architecture looks like.
Why Traditional Business Automation Is No Longer Enough
We have all been there—stuck in a rigid process that worked five years ago but hampers us today; identifying these limitations is the first step toward modernization.
Here is the hard truth about intelligent automation Dynamics 365: It exposes how fragile the old way really was. Conventional automation is a house of cards:
Rule-based workflows break: If the market shifts or a unique customer scenario arises, the script fails.
Humans are the error handler: Your team ends up spending valuable time fixing the automation's mistakes rather than doing high-value work.
No context: Standard bots don't know why a customer is important, only that a form was submitted.
This is where the "Labor Pyramid" creates a bottleneck. You can't just keep throwing bodies at the problem. You need agents that don't just automate steps. They automate thinking.
How AI Agents Transform Business Automation in Dynamics 365
Let’s get tactical. At congruentX, we believe technology should serve the outcome, not the other way around. Here is how this shift plays out in operations.
1. From Task Automation to Decision Automation
Traditional automation focuses on what to do. AI agents for CRM and ERP focus on why and when to do it.
I see companies automating the wrong things constantly. With AI agents, you can automate:
Lead prioritization: Focus sales efforts on prospects who are actually ready to buy, not just the ones who clicked an email.
Credit risk: Protect cash flow without stalling the deal.
Inventory replenishment: Order stock based on predictive trends, not just empty shelf space.
It’s about giving your people the freedom to focus on strategy while the AI handles the logistics.
2. Context-Aware Automation
The biggest killer of efficiency is the silo. usually fail because they don't see the big picture. An AI agent is different. A Supply Chain agent can see a delay and alert the Sales agent, who then manages the client expectations—all without a meeting. It breaks down the walls that keep departments fighting each other.
3. Natural Language Interaction
Stop forcing your team to learn complex interfaces. When the system speaks your language, adoption ceases to be a hurdle. You should be able to ask, "Why is customer churn increasing in the Northeast?" and get an answer, not a dashboard.
4. Proactive, Not Reactive
Traditional automation waits for a trigger. AI-powered workflow automation acts before the problem exists. It predicts churn, detects revenue leakage, and flags risks. It’s the difference between putting out a fire and installing a sprinkler system that senses heat.
5. Continuous Learning
This is my favorite part. The system doesn't depreciate; it appreciates. It learns from user behavior and outcome analysis. It’s a tool that actually gets sharper the more you use it.
Use Cases: Where the Rubber Meets the Road
We’ve seen firsthand how applying these agents changes the game. It’s not about "digital transformation"; it’s about practical application.
Sales: It’s not just data entry. It’s intelligent lead scoring and forecasting. It leads to higher win rates because the rep knows who to call and when.
Customer Service: It’s about sentiment analysis. The agent knows if a customer is angry before the human rep picks up the phone.
Finance: We move from transactional processing to strategic planning. The AI handles the invoice matching; the CFO handles the cash flow strategy.
Marketing: In digital transformation with AI, marketing stops being a cost center and becomes revenue intelligence. It segments audiences based on subtle behaviors that a human analyst would miss 9 times out of 10.
Business Benefits: The Bottom Line
Beyond the shiny features, we need to look at the P&L. Why does this matter?
Efficiency: It kills the busy work.
Accuracy: It removes the bias from decision-making.
Speed:Microsoft AI business automation gets you to ROI faster than building custom models from scratch.
Ultimately, these benefits compound. You end up with an organization that is leaner, faster, and smarter than the competition.
Buying Considerations: Are You Ready?
I always tell our clients that AI is a multiplier. If your processes are broken, AI will just break them faster.
You need to evaluate your readiness honestly. Do you have process maturity? Is your data clean, or is it a swamp? Do you have the governance in place? The good news is that Dynamics 365 provides a low-risk foundation, but you can't build on quicksand. We help you navigate this checklist so you aren't just buying shelfware.
AI Agents vs Traditional Automation
If you are still on the fence, look at the comparison.
Traditional: Rule-based, static, reactive. It’s a train on a track.
AI Agents: Intelligent, adaptive, proactive. It’s an ATV.
For businesses aiming to scale, the train track eventually runs out. The ATV doesn't.
Cost vs. Value
Cost is always a factor, but I encourage our partners to look at the cost of inaction. The total cost of ownership for AI agents is often lower than the labor hours spent fixing manual errors. You see reduced labor costs, better retention, and faster revenue realization. It pays for itself by plugging the leaks.
The Future is Agent-First
We are just scratching the surface. I genuinely believe that very soon, we will see agents collaborating with each other—Sales agents haggling with Inventory agents—without human mediation. Organizations adopting this today are future-proofing their operations.
Conclusion: Transforming Automation into Intelligence
Remember that question I asked at the start? “Are you building a system that follows orders, or one that thinks?”
AI Agents in Dynamics 365 represent a fundamental shift. But technology is only as good as the strategy behind it. At congruentX, we don't just implement tools; we align them with your unique business outcomes.
If you are ready to move from reactive management to proactive growth, we should talk.
Want to see how this works in practice?
Frequently Asked Questions (FAQ)
1. What are AI Agents in Dynamics 365?
Think of AI Agents in Dynamics 365 less as software and more as digital team members. Unlike old-school bots that just follow a script, these agents are autonomous. They analyze data, understand the context of a conversation, make judgment calls, and take action across your business processes. They don't just do what they're told; they figure out what needs to be done.
2. How are AI Agents different from traditional automation in Dynamics 365?
Traditional automation is a train on a track—it goes from A to B, and if there’s a rock in the way, it crashes. AI Agents for business automation are like off-road vehicles. They go beyond fixed rules, using machine learning and natural language processing to adapt to obstacles. They automate decisions and optimize outcomes without you having to constantly rewrite the code.
3. Which business processes benefit the most from AI-driven automation in Dynamics 365?
Look for the bottlenecks. The high-volume, data-heavy tasks where your highly paid humans are just moving bits around are the prime targets. Specifically:
Sales: Forecasting that isn't just a guess, and lead scoring based on reality.
Service: Resolving cases before the customer even picks up the phone.
Finance: predicting cash flow risks before they hit the bank account.
Supply Chain: optimizing inventory so you aren't sitting on dead stock.
Marketing: Personalization that feels human, not algorithmic.
4. Are AI Agents in Dynamics 365 suitable for mid-sized businesses?
Absolutely. This is the great equalizer. AI Agents in Dynamics 365 are scalable, meaning you don't need a Fortune 500 headcount to get Fortune 500 intelligence. It allows mid-sized organizations to punch above their weight class by automating complex processes without hiring an army of admins.
5. Do AI Agents replace human employees?
Let’s be clear: AI-powered business automation doesn't replace people; it replaces drudgery. It handles the repetitive data entry and the low-level decisions so your actual humans can get back to strategy, creativity, and building relationships—the stuff computers can't do.
6. How secure are AI Agents in Dynamics 365?
We aren't talking about open-source wild west AI here. AI Agents in Dynamics 365 operate inside the Microsoft ecosystem. You inherit that enterprise-grade security shield—data privacy, role-based access, and compliance are baked in, not bolted on.
7. What is the ROI of implementing AI Agents for business automation?
ROI usually hits within months, not years. It comes from plugging the leaks—missed leads, late invoices, churned customers. You see it in:
Reduced operational waste.
Decisions made in seconds, not days.
Revenue you can actually predict.
To get a clear picture of what that return looks like for your specific numbers, sign up for our Envisioning Lab to see how we can help improve your ROI,
8. Can AI Agents integrate with existing Dynamics 365 workflows?
Yes. You don't have to "rip and replace" to start seeing value. AI Agents in Dynamics 365 are designed to layer onto your existing workflows and Power Automate processes. It’s about enhancing the foundation you already have.
9. Do AI Agents require custom development?
Don't overengineer it. A lot of AI-powered automation in Dynamics 365 is ready out of the box. That said, if your industry has very specific nuances, some customization ensures the agent speaks your language. If you are trying to determine if standard features fit your vision,
10. How long does it take to implement AI Agents in Dynamics 365?
If your data is clean, we are talking weeks for a pilot. If your data is a swamp, we need to fix that first. Basic deployments are fast, while enterprise-wide transformation takes a few months. Timelines depend heavily on where you're starting from;
11. What data is required for AI Agents to work effectively?
Garbage in, garbage out. AI agents crave context—CRM records, emails, transaction history. The better your data quality, the sharper the agent's predictions and actions will be.
12. Is AI-powered automation in Dynamics 365 future-proof?
Static software depreciates the moment you install it. AI Agents in Dynamics 365 appreciate. Because they learn and adapt, they actually get better the longer you use them. Plus, Microsoft’s backing ensures compatibility as the tech evolves.
13. Should businesses invest in AI Agents now or wait?
Waiting for "perfect" is a strategy for irrelevance. The gap between the AI-haves and have-nots is widening every week. AI-driven business automation compounds over time; every day you delay is a day your competitors are gathering data and training their models. Now is the time to start.
