One of the Microsoft Dataverse features that doesn’t get a lot of attention is currency fields.
Too lazy to read? Check out the video version:
I have blogged about currency codes in Dataverse (then known as CDS) in the past (in 2020) and for the most part, the underlying technology hasn’t changed.
To quickly recap;
When you provision Dataverse, you choose a base currency.
There is a system currency table in Dataverse, you can define a record for each currency you would want to use in your application.
When you create a currency column for a Dataverse table, it will create 2-4 columns on your Dataverse table:
The currency field, the base currency, and a currency code and exchange rate columns (if they don’t already exist)
When you create a record with a currency field, a number of things happen:
The currency code will be populated based on the user’s preferred currency, by can be selected.
The amount entered will be the amount in the selected currency.
The base currency will be auto-populated based on the exchange rate.
The exchange rate will be set based on the current value of the record in the currency table.
Currency column considerations
There are a number of things to consider when using the currency functionality in Dataverse.
You need to manually set the exchange rate. The exchange rate is not automatically updated.
The exchange rate on a record doesn’t change automatically when updating the overall exchange rate.
Exchange rate will only be updated on a record if the amount or currency code changes.
Be careful when using currency fields in automation, report, or data integration, there is a difference between the currency column and the base currency column.
Knowing how currency fields and exchange rates work are important to understand when building your application, flow, or agent.
Updating exchange rates
In today’s topsy turvy global economy, the exchange rates fluctuate quite a bit. Keeping your exchange rates updated in Dataverse can become tedious.
A few years ago, I shared a method to maintain exchange rates using an API and a Power Automate flow. This is still a viable method, any you can get 100 free API reads per month.
Using AI Builder and Power Automate to extract and set exchange rates
I found another way to update exchange rates using AI Builder within Power Automate to read daily (or exchange rate alert) emails that contain exchange rate information.
I use a money transfer service called WISE (formerly known as Transferwise). One of the benefits is that you can sign up for daily exchange rate updates as well as threshold alerts. I work on international projects and get paid in different currencies, this service allows me to receive payment in my client’s currency and I can decide (not my local bank) when its beneficial to convert to my home currency.
Every day I receive an email in selected currencies telling me the exchange rate.
I created a Power Automate flow to read the email, use AI Builder to extract the rate and update the currency table in Dataverse.
The triggering action is when an email arrives from no-reply@wise.com
The first action is that I will send the body of the email to a custom AI Builder prompt. I will select the Create text with GPT using a prompt action. And then select + New custom prompt.
I entered the following prompt with a text parameter that would contain the body of the email.
Extract the exchange rate and the source and destination currency code from <<wise email body>>
I also tested and made sure the output was in JSON format:
I wired the parameter to read the body of the email from Wise.
The next step is to retrieve the currency code record from Dataverse. We do this to ensure we have the correct record to update.
And finally we can update the exchange rate in Dataverse with the current value. Note that I inverted (1/rate) to ensure I had the rate going the right direction. This may depend on how you configure the alerts from Wise.
Row ID:
First(body(‘Get_Currency_Record’)?[‘value’])?[‘transactioncurrencyid’]
Exchange Rate:
div(1, decimal(outputs(‘GetExchangeRate’)?[‘body/responsev2/predictionOutput/structuredOutput/exchange_rate’]))
Testing this was a bit tricky because I had to wait for an email from Wise to arrive in my inbox. Once I had some samples, I was able to test/retest the flow.
Note that I did get a warning about not having an content approval step following the GetExchangeRate step. It’s not a bad suggestion, but I don’t want to be bothered approving an exchange rate every day.
We can now see that the exchange rates are updated in Dataverse on a regular basis.
Note that the existing records (e.g. the Donations) are not updated unless the value or the currency code changes, and it will only reflect the current rate. If you need historical exchange rates based on a date, you will need to configure some extra functionality.
Summary
Microsoft Dataverse has a lot of lesser known, but useful features, such as currencies. These features become more powerful with you apply AI Builder and Power Automate.
Nick Doelman is a Microsoft MVP, podcaster, trainer, public speaker, and competitive Powerlifter. Follow Nick on X at @readyxrm or LinkedIN, and now; Bluesky. Listen or watch the the Power Platform Boost podcast with Nick and co-host Ulrikke Akerbæk every second week for news and updates from the Power Platform community.
Original Post https://readyxrm.blog/2025/05/01/ai-builder-update-dataverse-currency-exchange-rates/
