How to build/display currency conversion rates in Copilot Studio

September 2024

Remember your last trip to a country where you had to pay cash in the local currency? This happened to me on my last trip to Turkey. My base currency here in Germany is Euro, but I needed to pay the taxi driver in Turkish Lira for example. So no chance to pay with a credit card, I needed cash. Luckily there are ATMs everywhere, but the fees are quite high! It's better to be prepared and exchange at the best rate. But what does this have to do with Copilot Studio? Let's find out! 🥳

Table of Content

1. Scenario

2. Using public websites as Knowledge Source in Copilot Studio

3. Using Dataverse as Knowledge Source in Copilot Studio

4. Scraping data from websites using Power Automate

5. Building the topic in Copilot Studio

6. Bonus: Watch the Microsoft 365 & Power Platform Community Call Video

1. Scenario

First, you need to find a website where you can check all the reference euro exchange rates, such as the European Central Bank's site¹. Once you have defined the source, you can check the best possible exchange rate. But this would be a manual process and opening the website every time to check manually might not be what you want. Wouldn't it be cool to ask your own Copilot instead? 😏

2. Using public websites as Knowledge Source in Copilot Studio

The first idea that comes to mind is to use Knowledge Sources in Copilot Studio! Good idea! Let's see what possibilities there are!

It sounds like the most obvious solution to choose public websites! Sounds good, let's do that, but then we run into a problem.

The slashes in a URL indicate the depth, and once you are deeper than 2 levels, we cannot add the web page. Of course, we could add the entire European Central Bank as a whole, but we just want a specific scenario here. My awesome friend Sean Astrakhan also had a cool idea, could we use a URL shortener²? Tried it and unfortunately it didn't work, too bad! So what now? There's no other way, we need another solution. 😒

3. Using Dataverse as Knowledge Source in Copilot Studio

Dataverse is still in public preview, but has performed quite well in my recent tests. It also supports AI Builder prompts. First, we need to create a table in Dataverse. I initially named it Currency, but in my demo environment there was already a table Currency, which made things much more difficult in Power Automate. 😅 Reminder to myself, next time check if there is a table with the same name! Now the table has the rather unromantic name Currency-Demo. 😁

All field names in this demo are of type String, although it would have made sense for the Spot field to be of type ... for the Spot field. This requires 3 fields, as shown on the European Central Bank website: Currency, Name, and Spot.

In Dataverse:

Now we have our data structure, but the big question is: How do we get the data in? Manually? No way! We have to get the data from the website into our table somehow, but how? 🤔

4. Scraping data from websites using Power Automate

I've never scraped data from a website before, so I looked at YouTube tutorials. Shout out to Damien Bird for his video Effortlessly Scrape Data from Websites using Power Automate and Power Apps³ from about 2 years ago that explains how to build this pretty easily. Unfortunately, I couldn't rebuild his demo because you couldn't see the numbers in the source code of the bank page he used. But since I know Power Automate and I also want to store in Dataverse, it was still easy to implement.

Flow Overview:

Then let's go through the flow step by step.

1. Enter the URL and select the GET method to get the entire source code of the HTML page.

   

2. Use the action Compose to define an operaing tag. We use the expression nthindexOf(body('HTTP'), '<table', 1). This determines the index of the first “<table” found. We do this because we have the data in this area that we want to scrape.J

   

3. Now we have to find the bottom of the table. Same principle as before with nthindexOf(body('HTTP'), '</table>', 1).

   

4. Instead of the entire website source code, we now pack the relevant part for us into a table. We use the expression substring(body('HTTP'), outputs('ComposeOpeningTag'), sub(outputs('ComposeClosingTag'), outputs('ComposeOpeningTag'))). At the end, the table must be closed manually with a “</table>, as this is not included in the substring.

   

5. Now we convert the whole thing into an XML data structure: xml(outputs('ComposeTable')).

   

6. We convert again this time into a JSON data structure: json(outputs('ComposeXML')).

   

7. We use Compose one last time. In the end, we only get the data we need: outputs('ComposeJSONArray')?['table/tbody/tr'].

   

8. We load the data from the Dataverse Currency-Demo table. Note: To increase the performance, you can load only a portion of the data, not the entire Dataverse table, by setting a Fetch XML query or selecting columns for example.

   

9. To avoid duplicates in the database, we delete the data of the table once. Before we do this, we check if there is any data in the table, otherwise we would get an error. Alternatively, we could do an update, but this would result in ugly nested loops. However, this is important for performance. For the demo, however, this should be sufficient for now.

   

10. Now we have to put the data into Dataverse. We use the following expressions.

    • Currency: item()?['td']?[0]?['a/#text']

    • Name: item()?['td']?[1]?['a/#text']

    • Spot: item()?['td']?[2]?['a/span/#text']

    

This is a manual trigger in this demo, but you can also select a scheduled trigger, in which case it can be updated overnight, for example. 🤓

5. Building the topic in Copilot Studio

First, we check whether we can see the data in Copilot Studio.

Awesome! Now let's take a look at the two important topics here: Conversation Start and Conversational boosting.

Conversation Start:

This is where the Copilot takes his run-up, and this is where you can personalize it. Clippy makes his comeback!

Conversational boosting:

The cool thing is that all you have to do is set the Knowledge Source! Turn off Allow the AI to use its own general knowledge (preview) in Classic data.

Now let's test the whole thing by asking: What is the current rate for US dollar?

Hooray it works! 🎉🎉

6. Bonus: Watch the Microsoft 365 & Power Platform Community Call Video

The call will take place on September 26 at 4 pm CET, where I will present the whole thing live. Join me! Here is the link to register!

Update: Here is the video 🙃

Mahalo for reading! 🥳 #DropTheMic 🫳🏼🎤 If you enjoyed it, please subscribe so you don't miss the next article!

1. https://www.ecb.europa.eu/stats/policy_and_exchange_rates/euro_reference_exchange_rates/html/index.en.html

2. https://www.shorturl.at/

3. https://youtu.be/2kvSlh-Tvb4?si=kUs58QyuImXe3hcV