A No-Code AI Bot for Teams That Finds the Right Power BI Report Instantly

Author(s): Alexandr Vysotskiy

Originally published on Towards AI.

In this article, I’ll walk you through how I built an AI bot inside Microsoft Teams to help colleagues quickly find the right Power BI report. I’ll be honest — I’m not a Microsoft ecosystem expert, and I don’t build bots regularly. But when I ran into this problem, I couldn’t find a single practical guide online. So I’m sharing the solution that worked for me — it’s not perfect, but it works.

And you don’t need to be an engineer to create it.

💡 But first of all, why you need an AI bot?

• Hundreds or thousands of colleagues looking through hundreds or thousands of reports is very time-consuming.
• The AI understands meaning — e.g., it can suggest a Retention report when you ask about “Churn”.
• No matching report? It can suggest the right team to contact based on related topics or pre-trained instructions.
• It’s all in teams — lower entry barrier for new data enthusiasts.
• If your documentation is not limited to Power BI descriptions but also additional sources like gsheets or confluence — you can still have multiple sources for the bot’s knowledge.

🚧 Limitations / disclaimers

There was one constraint we had — paying for Microsoft Fabrics or some specific AI add-on within the Power BI was out of the picture due to the associated costs. The bot should be almost free.

That being said, we already had a metadata extraction tool in place, which helped us access report descriptions. It seems there are some alternatives to get the descriptions for free or almost free, like, Metadata Scanning API, Power BI Sentinel, etc, but this is not something we will be exploring in this article. For now, we assume, that we have the descriptions and dive into the details of bot creation.

🤖 What does the end result look like?

🛠️ Pre-requisites / tools

1. Teams
2. Copilot Studio (Microsoft) — this is where you can create a basic bot than get embedded into teams. It can do a lot of basic operations but can’t do complex things like sending API requests to third parties like OpenAI.
3. Power Automate (Microsoft) — this tool has native integration with the Copilot Studio and extends its functionality with the arsenal of hundreds of different connectors (including the one that can send and receive API requests from the OpenAI.
4. OpenAI playground — as you most likely already know, this is where you can create an assistant, similar to chatGPT, with its own instructions and access to specific knowledge.

On the high level, the flow looks like this:

You send a message in teams, the bot picks it up in the Copilot Studio and then uses Power Automate to chat with the OpenAI assistant via API. Pretty straightforward, runs every time there is a new question from a colleague. We will look into each one of these in a bit.

There is also a second flow, that is running on a schedule and is used to update the knowledge of the OpenAI assistant, but it is way simpler so we will not be looking into it any deeper.

Why not just use Copilot Studio alone?

It is important to mention, that Copilot Studio has access to one of the OpenAI models under the hood. So depending on your specific use case you might be more than happy to use the generative AI available within the Copilot Studio. It makes things much easier without having to worry about the steps handled by Power Automate and the OpenAI Playground.

There are a few reasons why you would still want to use the more complex approach and extend your bot with Power Automate and OpenAI.

1. You don’t get to choose the model Copilot Studio uses. And from my experience, having a direct connection to OpenAI often means you can have access to more recent models than those available through Copilot.
2. You don’t get to choose the Top P and Temperature parameters.
3. But most importnatly, for my personal use case, is that the output provided by the Copilot Studio’s genAI always links the knowledge source in the output message, redirecting people to the generated csv/txt files that only confuse the person looking for a report. With the direct access to the OpenAI API I could form the outputs more precisely.

Creating an OpenAI assistant

This is the heart of your future bot but there is nothing new here — you’ve probably read dozens of articles about ChatGPT and OpenAI Playground before. You basically name your assistant, give it some instructions like “Suggest a report based on descriptions and always attach a link to it”, choose your model, attach your knowledge source.

In our case, the knowledge source was a txt export of the report descriptions.

Once you outline the instructions and attach the txt file in the menu on the left — you can already chat with the assistant and get report recommendations. However, we now need to ‘wrap’ this assistant into something that can be accessible within teams.

Creating a flow in Power Automate

Power Automate has hundreds of different connectors to choose from. Everything from “Power BI” which you can use to connect to any of your reports or semantic models and query it using DAX, to plain API calls (we used them to communicate with the OpenAI assistant)

Here is an example of how your communication with OpenAI can look like:

1. We create a thread for the conversation.
2. We check the id of the thread we just created.
3. We send a message to that thread.
4. We choose which assistant to run against that thread.

There are more steps after this but they can be quite different based on your goals and set up.

Just to give you an idea what you can see ‘inside’ these steps, here is the configuration for the first step:

If you never worked with api — ChatGPT can help figuring out what to write in each of the api calls and which methods to use. Trust me, it helped me with this part.

The bottom line is that you need a Power Automate flow which can send a message to the Open AI assistant and receive the response.

Referencing the flow within our Copilot Studio Bot

Now, we can reference the Power Automate flow we created in the Copilot Studio bot.

The main page of your agent (yes, microsoft calls them ‘agents’) looks like this:

You would need to edit the ‘Topics’ to serve your needs. For example, you might want to edit the ‘Greeting’ topic to make the bot ask the user some questions about the report they are looking for.

No matter how you structure your agent, you can call that Power automate flow you created at any moment by clicking “add an action” -> “New Agent flow” (or rather in your case just select the already created flow).

There are many additional things that you can configure as part of the flows. For example, you might instruct it to ask the question “how satisfied are you with the recommendation?” every time after suggesting a report to collect NPS scores.

After doing so, your end result is that you have an agent in Copilot Studio which calls the Power Automate flow to chat with the OpenAI assistant. You can test your bot right within the Copilot Studio and publish it to Teams right in the settings.

✍️ P.S. This is my first Medium article, so I’d love your feedback — what worked, and what could be improved? Also, let me know if you’d be interested in elaboration on any of these steps in one of the following articles. Thanks in advance! 😊

Join thousands of data leaders on the AI newsletter. Join over 80,000 subscribers and keep up to date with the latest developments in AI. From research to projects and ideas. If you are building an AI startup, an AI-related product, or a service, we invite you to consider becoming a sponsor.

Published via Towards AI