However, these tools are highly generative by nature. While they sometimes deliver results in the desired format, this isn’t always the case. Similarly, they may autonomously complete tasks on occasion, but not consistently. Moreover, if you need data from a specific source—presented in a particular format that isn’t part of the LLM’s training corpus—you might find their capabilities lacking.

In such cases, programmatically retrieving data with defined inputs and outputs could be a more reliable solution. This is exactly where Actions in Copilot Studio come into play.

In Microsoft Copilot Studio, an Action is a reusable, customizable component that enhances an agent’s ability to perform specific tasks or respond to user queries. Actions are created using a low-code, graphical interface and can integrate with various systems, such as prebuilt connectors, Power Automate flows, or REST APIs. This allows agents to execute workflows, retrieve data, or deliver tailored responses efficiently.

In this post, we’ll explore how to build some basic Actions in Copilot Studio. To follow along, visit copilotstudio.microsoft.com and create a new agent. You can skip the configuration steps for now and navigate directly to the “Actions” section at the top. From there, click “Add an action” to begin creating your first Action.

Now, let’s look for an action to work with. There are plenty of choices here, each doing different things (think CRUD: some actions create records, others update or delete them, but for now, we’ll stick to read-only operations to get our footing).

Many of these are premium connections that need API keys or licenses, so we’ll look for one that’s fully open to the public. GitHub offers a few of those (which makes sense, given it’s owned by Microsoft).

So, search for GitHub and locate the action called “Lists all public repositories for an organization.” There are quite a few options, including a separate one for a user, so double-check to ensure you pick the correct one.

Next, we’ll configure how this action operates. I’ll set the end user authentication to rely on my access as the Copilot author, rather than the end user’s access, to avoid any discrepancies—though this shouldn’t be an issue here since GitHub is publicly accessible, but note that granting access to something a user wouldn’t otherwise have can have both advantages and drawbacks.

Next, let’s examine the inputs and outputs of this action. Copilot Studio will require one input—the repository owner—and deliver one output, called “Response,” which lists the repositories for that user. Understanding these inputs and outputs is crucial as we build more advanced agents.

Click “Add action” to incorporate this into our agent. A pop-up box should then appear, prompting you to “Go to Generative AI.” Go ahead and click it. If it doesn’t appear or you need to revisit it, navigate to Settings in the upper-right corner of your screen and select “Generative AI.”

Here, we’ll modify our agent’s setup to incorporate Generative AI for conversational interactions.

In Microsoft Copilot Studio, the traditional “Classic” agent operates using a structured approach where predefined topics are activated by specific user phrases, limiting responses to a rigid, preprogrammed framework.

In contrast, the Generative agent leverages advanced AI to dynamically interpret user queries and context, allowing it to intelligently select and combine topics, actions, and knowledge sources on the fly. This shift enables more organic, adaptable, and human-like conversations, as the agent can respond fluidly to a wider range of inputs without being constrained by exact phrasing or preset paths.

Save your agent, exit the Settings, and let’s put it to the test! You should see a sidebar for testing— if not, click the beaker icon labeled “Test” in the upper right to switch it on. Let’s chat here. I’m going to ask for the public GitHub repositories for Anaconda, the company that teamed up with Microsoft to integrate Python into Excel.

List the public GitHub repositories for Anaconda

We’ll observe in the Activity Map that this action is activated and delivers a response:

This is pretty cool, but the action itself is quite straightforward. It involves just one input variable and one output variable, leaving us with limited control over how the output is delivered. Plus, we didn’t need to stress about Copilot lacking the correct inputs or properly formatted data to execute the action.

So, let’s step it up and create a second action. I’ll head over to the Actions tab in Copilot Studio, remove our GitHub action, and set up a new one. Search for “Get current weather,” and you’ll find an MSN Weather action that, like before, doesn’t require user authentication (and, no shock here, is also a Microsoft product).

The first step I’ll take is to switch the end user authentication to Copilot user authentication:

This action is a bit more intricate, featuring two inputs and multiple outputs. The first input, location, isn’t too unexpected, but what about units? Is it really reasonable to expect the user to think of requesting weather conditions in a specific unit? To simplify things, we’ll go ahead and predefine that within the action itself.

Let’s modify a couple of things here. Under “How will the agent fill this input,” let’s change it to “Set as a value” instead of “Dynamically fill with best option.” This way, we’ll simply prefill this on the backend for the user.

Now we’ll explicitly select from the dropdown menu, choosing between Imperial or Metric. I’m American so I’m going with Imperial!

Next, let’s save and test the action. I’ll request the weather for Cleveland, OH, and the result will come back predefined in Imperial units:

These outputs might vary in format from one run to the next, though they’ll always be in Imperial units. Certain measurements, like wind gusts or visibility, may appear in some runs and not others.

If you’d like more control over what’s returned, you’ve got a couple of choices. One option is to remove some of the outputs from the action. There are 22 by default, so it’s a pretty long list to choose from! This would definitely reduce some of the unpredictability. Alternatively, for even greater precision, you could use the action within a topic, letting you capture and reuse parts of the user’s input and the agent’s output as variables. This approach is more exact and powerful, and we’ll cover it in a future post.

If you’re still wondering why we’d bother with an Action when it might seem like a regular LLM could handle the same thing, let’s break it down.

Actions in Copilot Studio aren’t just a fancy name for what an LLM already does. They bring something extra to the table. Unlike relying solely on a generative AI’s built-in knowledge, reasoning skills, or ability to search the web, Actions let developers create specific, step-by-step tasks that connect directly to external systems like APIs, databases, or business tools. This means you get exact, reliable results customized to what your organization needs.

To go back to our example of weather retrieval, an Action can pull that straight from a weather API, ensuring it’s fresh and accurate. A regular LLM, on the other hand, might guess based on old training data, misread your request, or grab outdated info from a web search.

LLMs are great at chatting and reasoning about general topics, but they don’t naturally “know” your company’s private data or the latest updates unless you connect them to the right sources. They might sound confident, but without that direct link, they could make assumptions that sound good but aren’t quite right. Actions solve this by acting like a bridge. They give the AI a way to “do” things, not just “talk” about them. You could use an Action to check inventory in a warehouse, process a payment, or fetch customer details from a CRM, all with precision that an LLM alone can’t guarantee.

What questions do you have about Actions or Copilot Studio more broadly? Let me know in the comments.

The post How to understand actions in Copilot Studio first appeared on Stringfest Analytics.

You can follow along by downloading the exercise file below:

This workbook contains multiple worksheets with related datasets typically encountered in a business setting. To help users easily navigate and organize the content, we’ve developed an Office Script that creates and updates a table of contents worksheet. You can use this script to simplify your workbook management. Just copy the script below and paste it into a new Office Script to get started:

Name your script something like “Add TOC, Check for Existing TOC” and save it. After saving, go to Script Details to explore the available options for sharing your script.

Sharing within a workbook

By default, your Office Script is configured to be accessible only to you in this workbook. To enable others to use it, you can modify the script’s access settings. If the script is being used often, particularly by those who may not be comfortable navigating to Office Scripts and running it manually, a convenient way to share it within the workbook is by adding a button, similar to how you would for a VBA macro.

You can right-click the button to modify, relocate, or make other adjustments. This setup is great for sharing and using the script within this specific workbook, but what if users want to use it in other workbooks?

Sharing with other users in your organization

To share your Office Script across your organization, I suggest accessing your OneDrive account online. By default, all Office Scripts are stored in the Documents folder under Office Scripts. From there, you can view and modify the sharing permissions of your files.

Sharing to SharePoint

You can move this script to SharePoint using the Move options, allowing it to be stored in a shared location. This makes it easier to reuse and manage across the organization!

Sharing outside your organization

One of the sharing options in OneDrive is to share the link with anyone. However, when opening an Office Script in OneDrive, it may not render correctly and instead display the raw JSON format of the script rather than the scripted function. This issue often occurs due to how OneDrive handles certain file types or how the script is accessed through the OneDrive interface. While the file can be saved locally and opened in Excel using the OSTS file extension, I find this process a bit cumbersome, to be honest.

As an alternative, for a quick and easily shareable script that displays nicely and is searchable, I recommend pasting your script into a GitHub Gist. Although this method won’t store the script in your OneDrive—it’s a copy-pasted version—it allows you to format the file with a .js extension, making it visually appealing. In fact, this is what I did with the original script I shared with you, which I’m including again below.

Since Microsoft owns GitHub, there may be more opportunities in the future to enhance integration between this feature and OneDrive or SharePoint.

Conclusion

Sharing your Office Scripts for Excel can greatly enhance collaboration and efficiency, making it easier for you and your team to automate tasks and streamline workflows. While the file type isn’t the most portable at the moment, with a little effort, you can get your scripts where they need to go.

Do you have any questions about sharing scripts or want to explore how Office Scripts can further elevate your Excel experience? Let me know in the comments!

Resources

• “Sharing Office Scripts in Excel,” Microsoft Support

The post Office Scripts for Excel: How to share your scripts first appeared on Stringfest Analytics.

These days? We have the godfather of Excel Bill Jelen making a tutorial on how to work in Excel with… JavaScript. JavaScript!

We also have the very real possibility that Python will be officially supported for Excel. Thousands have asked, and Microsoft is (probably) answering:

(Edit: Python in Excel is now possible as of August 2023!)

Why should you care?

As it turns out, both JavaScript and Python are open source tools, along with R, Spark, Docker and so many other tools powering the data boom. So we have data analysts using open source tools, many of them for the first time and often in conjunction with proprietary products such as Excel, Power BI, etc.

Why does this matter?

I believe that to be an educated user of data, you should have some idea of where your tools came from. As a gourmand, you probably want to know how your food was sourced, right? It helps you contextualize how the pieces of your craft come together.

Without some knowledge of provenance, you’re just a consumer. Learn a bit about the origin and philosophy behind the tools you use, and you’ll be that much savvier about how to use them right.

So here are some tidbits data analysts would benefit from learning about open source: some good, some questionable, all helpful in improving as an analyst.

It’s a different philosophy of ideas

First and foremost, open source software reflects an entirely different philosophy than proprietary: about the philosophy, economics and culture of information. As you might expect, there’s a lot of hair-splitting about what open source really means. One common distinction is what’s meant by calling open source “free.” Sure, it doesn’t cost anything — but something could be proprietary and still gratis. That’s why you may hear open source described as “free as in speech” rather than “free as in beer:” in other words, you are given protected rights to engage with the software however you see fit, rather than just consume it passively.

Enough semantics; let’s illustrate the primary difference between proprietary and open source with an example:

Imagine you stood on a street corner hawking copies of Excel. You figured that would be fine since you are throwing in a few of your own add-ins, right? How do you think Microsoft would feel about this venture of yours? They’ve got intellectual property protection against that stuff.

By contrast, with open source most if not all intellectual property restrictions are waived. You’d be perfectly welcome to add a few goodies to a download of Python and sell that (and you’ll see that’s exactly what one of the companies we’ll look at in a bit does).

If you’d like to learn more about what open source means, check out this article from Red Hat — one of the pioneers of the practice, by the way.

There’s a thriving aftermarket… paid and free

Red Hat makes a fascinating open source case study, because while it relies on “free” open source software, it is a publicly traded company. If it sounds crazy that one can build a business on open source… it’s actually become quite common!

For example, in Advancing into Analytics we take advantage of two free downloads from companies built atop open source: RStudio Desktop to work with R, and the Anaconda distribution of Python.

That’s the wild thing about open source: because the intellectual property is so loose, there’s plenty of opportunity for people to build commercial enterprises right atop it in ways that could get hairy with proprietary software.

Volunteer community

Another major difference (and this one is important for data analysts to keep in mind) is that historically, open source projects were maintained solely by volunteers. There’s not necessarily an official support channel or ticket you can file with these products.

Now, fortunately the market is providing for some solutions here — for example, many of the aforementioned “business of open source” companies will provide paid support licenses. There’s also been more visibility into the support and long-term planning of open source projects.

But it’s still something data analysts should perhaps consider before using open source on a project: things may be more fragile, and if something goes wrong they may need to be a bit more self-reliant in troubleshooting.

The do-it-yourself ethos runs high in open source in general, but that also brings about a large and vocal community. Open source users, developers and advocates can be opinionated and maybe a bit bossy to outsiders, but perhaps that’s because they are quite personally invested in these projects. We’ll talk a bit more about the organization of open source in a bit. But first, a bit more history.

The bad blood is over

Perhaps one of the most significant reasons data analysts hadn’t been using open source tools until recently is that Microsoft didn’t want them to! Nearly as soon as he’d co-founded Microsoft, Bill Gates wrote a famous “open letter to hobbyists” denouncing the rampant unauthorized distribution of software.

Perhaps Gates had a point, as this distribution more or less amounted to piracy. But what about software that was designed to be redistributed for free? Microsoft didn’t like that either, with later CEO Steve Ballmer in 2001 declaring the open source operating system Linux as a “a cancer that attaches itself in an intellectual property sense to everything it touches.”

Flash forward a few years and Microsoft admitted it was wrong on open source; among other actions, it’s released a full Linux kernel for Windows and has open sourced many of its own products like Visual Studio Code and PowerShell.

In fact, it’s even been said on more than one occasion that Microsoft Linux…

Microsoft admitted there are many things admirable about open source, and that many open source projects offer features that complement their own quite nicely.

For example, Power BI supports R and Python for data visualization, data cleaning, machine learning and more. In fact, I have a course on R for Power BI users course for you to check out if you’re interested:

And, as mentioned earlier, there are now ways to work with JavaScript and Excel Online. As I like to say, Microsoft wouldn’t be spending millions of dollars to make these tools available if there weren’t any benefit to using them. Admitting the benefits of these open source tools is a win for everyone.

It moves quickly

Have you noticed that tech moves kind of quickly these days? I’m really not that old, but as a college student I didn’t have a smartphone or tablet or GPS. Could you imagine a student these days operating without those?

As technology rapidly changes, so does the code needed to power it. Code that is open sourced for anyone to develop and tweak can move much faster — for better or worse. Yes, this can be a bout of “move fast and break things.” This goes back to the idea of proprietary software being arguably more stable.

In many cases, there may be a cutting-edge data science approach that just hasn’t been fit into a proprietary tool yet. Open source does let software move at the speed of research. I would say this has been a critical factor in raising the status of data science in modern business.

This CNBC video does a great job exploring just way open source has become so dominant today:

One theme touched on by the video is the role of GitHub in facilitating open source development. This is a theme echoed by a fantastic book on open source, Working in Public by Nadia Eghbal. GitHub in many ways has become the common denominator for distributed contributors to work together on open source, such that learning a bit of GitHub is a de facto rite of passage for any programmer today.

And hey, would you look at that: Microsoft now owns GitHub. Again, it’s almost like Microsoft wants its customers to be able to use open source these days…

Get a bigger perspective on tech

Chances are very high that something your organization uses is open source. In fact, Red Had found that 90% of IT respondents reported enterprise open source is being used.

If you work with any data scientists or engineers, they’re almost certainly using open source. By understanding through experience how it works, its pros and cons, you’ll be in a much better place to collaborate effectively with them.

As you’re seeing, open source has been a crucial element to today’s tech boom, so much that I would argue that any serious technical professional (and I’d consider data analyst a candidate here!) should have at least some exposure to it!

Sounds interesting… now what?

Glad I piqued your interest! Here’s what I suggest:

• Consider some of the types of tools you use regularly on the job: spreadsheets, databases, ETL tools, etc. Do some product research. Which are open source and proprietary? What ramifications are there?
• Most open source analytics tools are code-based. Learn a bit! Understand enough to know what a distribution is, what packages are, etc. See what type of projects are going for that language on GitHub.
• Take a look at the proprietary software you’re using. Are there opportunities to integrate open source tools with it? Many vendors have followed Microsoft in encouraging open source work.

Whatever software you use for analytics — it didn’t just fall from the sky. It was developed by people, under a contract, for good and bad. With as much impact it’s made on the world, it’s worth data analysts learn a bit about the wild world of open source and even experience it a bit for themselves. Chances are more likely that they can (or must) use it for a future project. If Mr. Excel’s doing it… you probably should too.

The post What data analysts should know about open source first appeared on Stringfest Analytics.

That’s where a class can come in handy, and I’m excited to share that I’m working on a companion course to the book.

It culminates with what I call the “Full-stack Analyst.” There you’ll build an end-to-end data analysis project as part of the class using a dataset of your choice, ready to share with the world on your own GitHub repo.

You’ll receive things like:

• Demo notes and checklists
• Practice activity bank with solutions
• One-on-one project review
• Signed book and swag
• Certificate of completion 

Learn more about the course here.

You can also sign up for the wait list below to be notified about course updates:

If you’d like a framework for conducting analysis, or are interested in further breaking down barriers between your skills in Excel, R and Python, this course is for you. 

I look forward to continuing with you on your analytics journey. 

Learn more about Advancing into Analytics, including how to read for free, here.

The post Advancing into Analytics course wait list first appeared on Stringfest Analytics.

No, most purchases these days allow you to “try before you buy.” And why shouldn’t a book be the same way? Reading a book is a real commitment, especially a technical book like Advancing into Analytics. Fortunately, there are some ways to kick the tires before you take it on the mental highway:

Get a free trial of O’Reilly Online Learning

O’Reilly Media (the book’s publisher) has a fantastic Online Learning portal. Previously known as Safari, it features books (like mine), webinars, interactive coding exercises, and more. And it’s not just O’Reilly content, but most of the leading technical publishers’ too.

Many universities and workplaces already have an O’Reilly subscription, so ask around for access. If you don’t have it, you can still get a free 30-day trial to the platform here. I bet you’ll be hooked by the end of it.

The following video provides a quick walkthrough of the platform and how to access the book (including how to leave a review… pretty please?).

Check out the repo

One cool thing about O’Reilly’s books is that the companion repository is open to the public. Here you can see all the datasets, scripts, and other assets used for the book’s explanation and exercises. You can download a copy to your computer or, if you’re familiar with GitHub, clone it.

Of course, it can be hard to know what’s going on without the book by your side, but this at least gives you a taste for what your end learning state would look like.

Attend a book event

Book events can be seen as a preview in themselves. There you can learn more about the book and possibly even hear me read from excerpts or walk through demos from it.

This is going to be a near-completely online book launch, so I hope you can find some opportunities either near or far to come experience parts of the book at an event before you decide to purchase.

To learn more about upcoming events, check out the blog for updates or subscribe to my newsletter.

Borrow it from your library

As I mentioned earlier, if you are at a university you may already have library access to O’Reilly Online Learning.

Regardless, I think a lot of people aren’t aware that you can request your library order a given book. Try a web search for Your library name + materials request and you’ll likely find a form to do it.

Now that you’re there, why not ask your library to purchase the book? Here’s all the info you’ll probably need:

Author: Mount, George
Title: Advancing into Analytics: From Excel to Python and R
Publisher: O’Reilly Media
Release date: April 2021
ISBN-13: 978-1492094340
ISBN-10: 149209434X

I guess this is a loophole that authors don’t want to talk about, but library rentals only last so long — I’ve bought my fair share of books after getting a glimpse of them from the library.

Thank you for trying it out

I know not everyone wants to shell out for a data analytics book. I won’t take it personally if you don’t buy.

But if you have any interest in at least skimming the book, or want to share it with someone who might, please use these techniques to learn a little more about it. It’s not about the money — financially, you often do authors a bigger favor paying for a beer than buying their book. It’s really about making the biggest impact on the community as possible.

Thank you for checking it out, and best wishes on your analytics journey.

The post Advancing into Analytics: Try before you buy first appeared on Stringfest Analytics.