Home
Dynamics 365
Business Central
From Business Central to Excel: How Copilot and Agents automate everyday data work

From Business Central to Excel: How Copilot and Agents automate everyday data work

Renato FajdigaBusiness CentralYesterday32 Views

Hello 🙋‍♂️,

in this blog post, I am going to reflect on how end users could benefit from using Agent in Excel, either by connecting to it or by using data from Business Central. We see that organizational needs for using flexible and faster analytics tools like Excel are growing day by day.

In Dynamics 365 Business Central, there are many ways for users across different functions to build reports, perform analysis, and gain insights from data. Often, the most-asked question from end users is: “Can we export this to Excel?” By that, we often face situations where Business Central data ends up in Excel, and this blog is about that scenario: how Excel can enrich Business Central data for some custom scenarios.

🔽 Extract data from Business Central

There are many ways in how Business Central data can be consumed by Excel directly:

Using the Open in Excel functionality in list pages to export data from Business Central.
Creating configuration packages and exporting Business Central tables to Excel.
Using standard or developing custom queries and then using Open in Excel functionality to export data from the Business Central UI
Connect Excel and Business Central via OData APIs, then use simple extract, transform, and load with Power Query.

🤖 Agent in Excel

With each of the methodologies mentioned above, you can easily bring Business Central data into Excel for further analysis/understanding, data cleansing, data preparation for migration, etc.

All those tasks can sometimes be heavy, causing a lot of manual cleanup, manual tasks, and slow data analysis, and making it difficult to make decisions based on ERP data.

To speed things up a bit, Microsoft introduced Copilot in Excel a while ago, and just recently, users gained the ability to use Agents in Excel.

Agent in Excel empowers users to write tasks in natural language and then, together with the user, craft an end product – a powerful spreadsheet for data analysis.

🏬 My story

In my case, I gave myself the task of analyzing a large dataset of our fixed assets (over 8000 rows) and using Excel Agent to determine the overall budget for IT equipment replacement.

The query was simple: find all assets older than 1 year, group assets by employee, and set any asset older than 1 year for renewal.

All data on Fixed Assets is in Business Central, and I thought this should not be a challenging task.

I chose option 3 and built my custom query to extract data from Business Central to Excel.

As I have connected Dynamics 365 Business Central with One Drive, Excel with Business Central data automatically opens in my browser.

Now to navigate to Copilot and Agents, you must select Copilot. In Copilot pane, select Tools > Edit with Copilot

And I started prompting, and the first thing I tried was my simple query: “find all assets older than 1 year, group assets per employees and if asset is older than 1 year set this asset for renewal.”

But this was so vague that I only got information on who has to order a new laptop.

Then I started thinking more as a user and extending my query into a (what I thought was) solid prompt: “You can find here list of our fixed assets. Can you analyze it and group all fixed assets per employee. For assets that are laptops or phones check if laptop or phone is at least 1 year old and if yes then propose if person has to order new device and add in new column link to Amazon Webshop model. Keep preferences and employees that are using Apple should stay with Apple device and others are flexible. Proposed models must be the latest available from perferred manufacturers. Also all Apple macbooks must be with new M5 chip.”

And that was quite interesting, and I got picture-perfect data – even though I had to wait for 4 minutes for the Excel Agent to process my prompt. By default, I was making typos and not using very clean English 😊

Then I started thinking: ok, now you want to know easily how much it will cost you and what budget you will need for this heavy replacement. Plus, as an Excel Agent always add new columns to my original sheet. I wanted a new sheet with a clear breakdown. Also, I noticed that Agent was starting to use different Amazon webshops (.de, .com, .fr, .it), so I gave input that all prices must be converted to EUR and the exchange rate from the ECB.

At the end, I extended my query with the following:

“You can find here list of our fixed assets. Can you analyze it and group all fixed assets per employee. For assets that are laptops or phones check if laptop or phone is at least 2 year old and if yes then propose if person has to order new device and add in new column link to Amazon Webshop model. Keep preferences and employees that are using Apple should stay with Apple device and others are flexible. Proposed models must be the latest available from perferred manufacturers. Also all Apple macbooks must be with new M5 chip. Based on proposal, calculate total budget needed for all replacements based on price from the webshop. Split per device type and manufacturer. Also prepare clean new sheet with list of Responsible Employee, Proposed Model, Link to Amazon Webshop and cost. If the cost/price is in USD convert to EUR based on ECB exchange rate.”

And after 24 steps, I was in shock – my Excel Agent died 🤦‍♂️ (RequestPayloadSizeLimitExceeded), but the Agent left some messages and leftovers

And if you ask me, seeing this prompt I would also 😄

So I acted as Excel Agent asked, and at the end, the result was not satisfying and what I was looking for, so I deleted everything and start thinking how can I help Excel Agent to work for me – so I came with in my mind super idea – if my prompt killed Excel Agent, let someone else help Excel Agent in natural language for agents. I engaged Copilot and added my prompt that killed Excel Agent and asked Copilot: “Can you please optimize my prompt and tailor it for Excel Agent so that it looks like the user was writing it?”

New prompt 👇

I have a list of our fixed assets in this workbook. I need you to analyze the data and organize all fixed assets by employee. For each employee, identify their laptop and phone, and check whether each device is at least 1 year old. If a device is 1+ years old, propose a replacement model.

When proposing replacements, keep the following rules in mind:

Employees who currently use Apple devices should stay with Apple.
Other employees can receive any brand.
All proposed models must be the latest available from the preferred manufacturer.
All Apple MacBooks must use the new M5 chip.
For every proposed model, include a link to the corresponding Amazon webshop product page.

After that, calculate the total budget needed for all replacements based on the webshop prices. Provide a breakdown by device type (laptop, phone) and by manufacturer. If any price is in USD, convert it to EUR using the current ECB exchange rate.

Finally, create a clean new sheet that includes the following columns:

Responsible Employee
Proposed Model
Amazon Link
Cost in EUR

Make sure the sheet is formatted clearly and ready for reporting.

How does this look much nicer than my prompt? Of course it does, and I think we all can learn something from this.

Above is a screenshot of a new data source – with green (1) I marked data from Business Central, and with yellow (2) I marked data that was received after the Excel Agent finished the work. Some of those fields are “technical” fields that will help Excel Agent better understand the data and build additional reports, while others are related to links that let the user easily open each model.

So let’s see other results from Excel Agent – the first new sheet Excel Agent created was Device Catalogue, together with information about the exchange rate from the ECB.

The second sheet was a Replacement proposal sheet with details about the employee, the proposed model, a link to the Amazon webshop, and the cost in EUR.

The third sheet was the Replacement Budget Summary, where Excel Agent calculated the number of replacements needed and the total cost for budget allocation.

And the last sheet is Cost per employee pivot table.

IMHO, what Excel Agent did in the last iteration with a good prompt was exactly what I was looking for (all data is anonymized and a bit crunched). In total, to create the final table and all sheets, Excel Agent spent 6 minutes while I was watching YouTube videos.

🧑‍🏫 Key takeaways

Get familiar with the available tools in M365 & Copilot – it will make your working time more efficient.
Things you once thought were not possible, now with AI, Agents are becoming a reality – applicable to almost all tools and scenarios.
Be precise in your prompts. Learn how to prompt. The agent is not your chit-chat friend; you should give the agent clear instructions.

Some other prompts that I really liked during this demo: