When your Inbox becomes your job – automate it and forget it

What if you had to reply to more than 200 emails every single day?

During one of my training sessions, a participant caught my attention — or rather, he didn’t pay attention. He spent most of the day staring at Outlook instead of the data analytics case I was showing. At first, I thought he simply wasn’t interested in the course. Eventually, I asked why he had signed up if he was going to spend the entire day answering emails.

His answer was disarming:

“I have to reply to more than 200 supplier emails a day. If I stop, the whole process stops.”

That conversation gave me an idea… So, dear participant — if you’re reading this — here’s how you can automate your job and finally reclaim some time to actually think.

Create a Power Automate flow to collect commodities prices

The purpose of the flow we are about to build is to automate the collection, validation, and central storage of commodity market prices, making real-time data available for procurement and business decision-making.

By converting all prices to a standardized format and filtering out invalid data, the flow ensures that only meaningful and actionable information enters a Dataverse table.

First step – Create a Dataverse table to store the commodities prices, updated every hour

To store the commodity prices, those relevant to the fuels business, we chose Dataverse for the job, and created table CommodityPrices with the following columns:

Column	Description	Data Type
Symbol	Short symbol representing the commodity (e.g., CORN, CPO, SOYBEAN). It’s used for quick reference and API mapping.	Text (Single Line)
Product	Full name or description of the commodity (e.g., Corn (ag proxy), Crude Palm Oil, Soybean (proxy UCO)).	Text (Single Line)
PriceRaw	The raw price returned directly by the API, before unit conversion.	Decimal Number
RawUnit	The measurement unit of the raw price (e.g., per bushel, per ton). This field helps track how each commodity is quoted on the market.	Text (Single Line)
PriceEURperTon	The normalized price in €/ton, used for all internal comparisons and calculations. This value is computed automatically during data ingestion.	Decimal Number
Currency	The currency of the converted price (normally EUR).	Text (Single Line)
CollectedAt	Timestamp of when the data was collected from the API. Useful for monitoring updates and validating refresh frequency.	Date and Time
Source	The data source or API provider (e.g., Commodities-API).	Text (Single Line)
RawJson	The full JSON payload returned by the API call, stored for traceability, debugging, and validation of future conversions.	Multiple Lines of Text

This table stores real-time market data for key commodities used as feedstock or pricing benchmarks in the biofuels industry. It is refreshed automatically every hour via API. It acts as the foundation for the market price reference system.

Subsequent Power Automate flows read from this table to compare supplier offers, calculate price deltas, and trigger alerts or approval workflows in Power Apps.

Second step – get an API key from commodities-api.com

To collect live commodity prices automatically, you need an API key from commodities-api.com. This key authenticates your requests and tracks your usage plan.

1. Create an account

Go to commodities-api.com and click Sign Up. After registration, your Dashboard will show your personal API key (a string similar to 2f3b7c…). This key must be included in all HTTP requests, for example:

https://api.commodities-api.com/api/latest?access_key=YOUR_API_KEY&symbols=SOYBEAN,CORN,CPO&base=EUR

2. Understand the available plans

The platform provides several pricing tiers depending on how many calls you need per month and how frequently data should be refreshed.

Plan	Monthly Price	Calls/month	Rate limit	Symbols/request	Update frequency
Pro	$49.99	1 000	60 calls/minute	Up to 5	~10-minute updates
Pro Plus	$99.99	10 000	120 calls/minute	Up to 5	Every 60 seconds
Professional Advanced	$199.99	20 000	120 calls/minute	Up to 10	Every 60 seconds
Plus	$499.99	50 000	240 calls/minute	Up to 20	Every 60 seconds

3. Store the key securely

Instead of hard-coding the key in Power Automate, save it as Power Platform environment variable.

Third step – create the Power Automate price collection system

We are going to collect prices for commodities from the API every hour, so the trigger will be scheduled accordingly:

Next, we store the base URL, the API key, the base currency and the symbols of the commodities we need the prices for in variables:

Here are the variable definitions:

varAPIBase, type String:
https://commodities-api.com/api/latest

varApiKey, type String:
@{parameters('APISecretKey (nn_APISecretKey)')}

Note that you must first create a solution that includes this flow and the environment variable “APISecretKey” in order to make it accessible here.

varBaseCcy, type String:
EUR

varSymbols, type Array:

[
"SOYBEAN",
"CORN",
"CPO"
]

Next, we need to call the API, using an HTTP request, as follows:

The code in the URI is:

concat(variables('varApiBase'),
        '?access_key=', variables('varApiKey'),
        '&base=', variables('varBaseCcy'),
        '&symbols=', join(variables('varSymbols'), ','))

This creates the complete URL, including the API key, the currency we want the prices in and the array of symbols as a string separated value.

So, the flow, by now, should be like:

The next step will analyze the response we get in JSON format from the API call:

The Content is:

body('HTTP')

And the Schema is:

{
    "type": "object",
    "properties": {
        "data": {
            "type": "object",
            "properties": {
                "success": {
                    "type": "boolean"
                },
                "timestamp": {
                    "type": "integer"
                },
                "date": {
                    "type": "string"
                },
                "base": {
                    "type": "string"
                },
                "rates": {
                    "type": "object",
                    "properties": {
                        "CORN": {
                            "type": "number"
                        },
                        "CPO": {
                            "type": "number"
                        },
                        "SOYBEAN": {
                            "type": "number"
                        }
                    }
                },
                "unit": {
                    "type": "object",
                    "properties": {
                        "SOYBEAN": {
                            "type": "string"
                        },
                        "CORN": {
                            "type": "string"
                        },
                        "CPO": {
                            "type": "string"
                        }
                    }
                }
            }
        }
    }
}

The response we get from the API is something like the following example for CPO:

{
  "data": {
    "success": true,
    "timestamp": 1761667860,
    "date": "2025-10-28",
    "base": "EUR",
    "rates": {
      "CPO": 0.0011332695018226
    },
    "unit": {
      "CPO": "per ton"
    }
  }
}

Now, we need to create three Compose actions to extract the base currency, the rates object which contains the price and the time stamp. We do as follows:

The expressions of each of these actions are:

body('Parse_JSON')['data']['base']

body('Parse_JSON')['data']['rates']

body('Parse_JSON')?['data']?['timestamp']

Then, we’ll have to iterate over each of the commodities array items and push the results we get from the API into the Dataverse table. For that, we’ll need an “Apply to each” action, like:

The source of the Apply to each action is the variable we defined previously:

variables('varSymbols')

Since the units of each price may differ from commodity to commodity, we need to convert everything into euros per ton. For that, we’ll add three additional Compose actions, as seen on the screenshot above.

Compose cmpPrice:

coalesce(body('Parse_JSON')?['data']?['rates']?[items('Apply_to_each')], 0)

This will extract the price as it is.

Compose cmpUnitForSymbol:

coalesce(
  body('Parse_JSON')?['data']?['unit']?[items('Apply_to_each')],
  'unknown'
)

This action will extract the unit, like: “per ton”, “per bushel”, etc.

Compose priceEURperTon:

if(
  equals(items('Apply_to_each'),'SOYBEAN'),
  div(float(outputs('Compose_cmpPrice')), 0.027216),
  if(
    equals(items('Apply_to_each'),'CORN'),
    div(float(outputs('Compose_cmpPrice')), 0.0254),
    float(outputs('Compose_cmpPrice'))
  )
)

And this action will convert all the prices into euros per ton.

Lastly, we’ll use a condition before inserting into Dataverse

After normalizing all commodity prices to euros per ton, you’ll often want to filter, validate, or trigger specific actions only when certain criteria are met. This is achieved using a Condition action.

The condition configuration is as follows:

outputs('Compose_cmpPrice')

Operator: Is greater than

Value: 0

If this condition evaluates as True, Power Automate executes the next step: Add a new row in Dataverse. This action inserts a fresh record in the Dataverse table using the normalized values computed earlier for symbol, product, raw price, unit, price per ton, currency, source, timestamp, and JSON payload.

If the condition is False (e.g., price is zero or not returned), the flow skips the insert, thus keeping the table clean and trustworthy.

This conditional insert ensures only valid, up-to-date prices are logged in Dataverse, supporting real-time analytics and procurement decisions for your team.

So, the result, after a few Power Automate runs through the day, will be like:

As you can see, the API is returning the data we need to create a decision model. This will allow us to compare the supplier’s offers to the market prices and automate decisions.

Scan the supplier’s emails and compare prices with the database

We will now create a separate Power Automate flow dedicated to reading incoming emails from suppliers containing quotations and offers. This new flow will automatically extract the quoted prices for relevant raw materials directly from the body of the emails or attached documents.

The extracted data will then be parsed and standardized, enabling seamless comparison with the real-time market prices stored in the Dataverse database. By integrating both flows, procurement teams can instantly benchmark supplier proposals against current market conditions and make data-driven purchasing decisions with minimal manual intervention.

First step – Create a dedicated Outlook folder

Start by creating a folder in Outlook named “Supplier Quotes” or something similar. Suppliers should be asked to send their offers to a specific address (e.g., procurement@company.com), which automatically moves incoming messages to that folder using Outlook rules.

Second step – Create a new Power Automate flow within the solution to be triggered when a new email arrives at this folder

The trigger will look like this:

We include the Attachments, as they may have important information. Also, only the emails sent to procurement@swell.com.pt will be read to avoid triggering irrelevant emails.

Now, we’ll create a Compose action to retrieve the contents of the email.

concat('Subject: ', triggerOutputs()?['body/subject'], '\nFrom: ', triggerOutputs()?['body/from'], '\nBody:\n', triggerOutputs()?['body/body'])

This will concatenate the subject with the sender and the body of the email with the quotation.

The next step within this flow will be to create and run a prompt that will receive as input the result of the previous Compse action. These are the instructions we’ll be passing on to it:

SYSTEM:

You are a careful information extractor for supplier quotations in the fuels/biofuels industry.

Output STRICT JSON only, no markdown, no commentary.

USER:

Extract from the email below the quotation(s) and normalize fields.

If numbers use commas as decimals, convert to dots.

Normalize currency to “EUR” or “USD”.

Normalize unit to one of: “per ton”,”per mt”,”per kg”,”per liter”,”unknown”.

If quantity/unit absent, set null.

If multiple products/prices in one email, return an array with multiple items.

Return this EXACT JSON shape:

{
"supplier_name": string | null,
"supplier_email": string | null,
"offers": [
{
"product_text": string, // e.g., "Biodiesel (B100)", "UCO", "CPO"
"symbol_guess": string | null, // e.g., "B100","UCO","CPO","CORN","SOYBEAN","DIESEL","GASOLINE"
"unit_price_value": number | null, // numeric, dot decimal
"unit_price_currency": "EUR"|"USD"|null,
"unit_price_unit": "per ton"|"per mt"|"per kg"|"per liter"|"unknown",
"quantity_tons": number | null,
"incoterms": string | null, // e.g., "CIF", "FOB", etc.
"location": string | null, // e.g., "Vila do Conde"
"delivery_time_days": number | null,
"payment_terms_text": string | null,
"notes": string | null,
"confidence_0_1": number // 0..1
}
]
}
EMAIL:

We are using GPT-4.1 mini as the model to extract in JSON format the quotation of the commodities we’re interested in. We test the prompt with the following sample email:

Subject: Weekly Biofuels Quotation – UCO & B100
From: EcoEnergy Partners offers@ecoenergy-partners.com

Dear Procurement Team,

Below our updated weekly quotation:

Product
Quantity
Unit Price
Terms
Delivery Location

UCO
600 MT
€935 per ton
FOB
Sines

Biodiesel (B100)
400 MT
€1,065 per ton
CIF
Aveiro

Payment terms: 30 days after delivery.
Valid until: 04 Nov 2025.

Best regards,
Alice Keller
EcoEnergy Partners GmbH

This can be done by entering the above text content into the Sample data area in the Text Input parameter of the prompt:

And by hitting the test button, we get the Model response right away:

It looks pretty good to start with. Save the Prompt definition and go back to Power Automate. Now, add the outputs of the previous Compose action into the Text input field:

So far, this flow looks like this:

We now need to explore the results of the Prompt, normalize the results and prepare them to add a new row to a new Dataverse table (more on that later). So, here’s a concise walkthrough of the actions right after Run a prompt, with the exact expressions.

1) Compose – cmpJsonText
Gets the JSON string returned by the prompt.

body('Run_a_prompt')?['responsev2']?['predictionOutput']?['text']

2) Parse JSON
Turns that string into an object.

json(outputs('Compose_cmpJsonText'))

(Schema = the offers schema you defined.)

3) Apply to each (offers)
Iterates each extracted offer:

body('Parse_JSON')?['offers']

Inside the loop:

Compose – cmpProduct

items('Apply_to_each')?['product_text']

Compose – cmpSymbol

coalesce(items('Apply_to_each')?['symbol_guess'], 'OTHER')

Compose – cmpUnitPrice

float(items('Apply_to_each')?['unit_price_value'])

Compose – cmpCurrency

toUpper(items('Apply_to_each')?['unit_price_currency'])

Compose – cmpUnit

items('Apply_to_each')?['unit_price_unit']

Compose – cmpQuantityTons

coalesce(items('Apply_to_each')?['quantity_tons'], null)

Compose – cmpIncoterms

coalesce(items('Apply_to_each')?['incoterms'], null)

Compose – cmpLocation

coalesce(items('Apply_to_each')?['location'], null)

Compose – cmpDeliveryTime

coalesce(items('Apply_to_each')?['delivery_time_days'], null)

Compose – cmpPaymentTerms

coalesce(items('Apply_to_each')?['payment_terms_text'], null)

Compose – cmpPriceEURperTon
Converts to €/ton (handles currency + unit in one shot).
Requires a variable varUsdToEur (e.g., 0.93) if USD appears.

if(
  or(
    equals(items('Apply_to_each')?['unit_price_unit'], 'per ton'),
    equals(items('Apply_to_each')?['unit_price_unit'], 'per mt')
  ),
  float(items('Apply_to_each')?['unit_price_value']),
  if(
    equals(items('Apply_to_each')?['unit_price_unit'], 'per kg'),
    mul(float(items('Apply_to_each')?['unit_price_value']), 1000),
    if(
      equals(items('Apply_to_each')?['unit_price_unit'], 'per liter'),
      div(float(items('Apply_to_each')?['unit_price_value']), 0.88),
      float(items('Apply_to_each')?['unit_price_value'])
    )
  )
)

Compose cmpMarketSymbol

coalesce(
  if(equals(toUpper(items('Apply_to_each')?['symbol_guess']),'UCO'),'SOYBEAN',
    if(equals(toUpper(items('Apply_to_each')?['symbol_guess']),'B100'),'CPO',
      if(equals(toUpper(items('Apply_to_each')?['symbol_guess']),'CPO'),'CPO',
        if(equals(toUpper(items('Apply_to_each')?['symbol_guess']),'CORN'),'CORN',
          if(equals(toUpper(items('Apply_to_each')?['symbol_guess']),'SOYBEAN'),'SOYBEAN', null)
        )
      )
    )
  ),
  'SOYBEAN'
)

That’s it—these actions take the model output, normalize it, and produce a clean €/ton price plus the metadata you’ll store/compare next. Here’s an updated version of the flow, as it is now:

Third step – create a new Dataverse table: SupplierQuotations

The Supplier Quotations table stores all quotations automatically extracted from supplier emails.

It is designed to centralize, normalize, and compare quotations for biofuel and commodity purchases — such as Biodiesel (B100), Used Cooking Oil (UCO), Crude Palm Oil (CPO), Corn, or Soybean — against the current market prices stored in the CommodityPrices table.

Each record represents a single quotation line from an email, enriched with normalized data (for example, all prices converted to €/ton) and linked to market data for automated analysis in Power BI or Power Apps.

General Information

Display Name: Supplier Quotations
Logical Name: cre05_supplierquotations
Primary Name Column: cre05_name
Primary Name Format: AutoNumber → SQ-{SEQNUM:6}
Description: Table that stores supplier quotations automatically parsed from incoming emails using a Power Automate flow integrated with a GPT prompt. The table allows procurement teams to monitor offers, compare them to real-time market prices, and identify potential opportunities or risks.

Column Definitions

Below is the full specification of the columns included in this table:

• Supplier Name (cre05_suppliername) – Text (Single line)
• Supplier Email (cre05_supplieremail) – Email
• Product Text (cre05_producttext) – Text (Single line)
• Symbol (cre05_symbol) – Text field (B100, UCO, CPO, CORN, SOYBEAN, DIESEL, GASOLINE, OTHER)
• Unit Price Value (cre05_unitpricevalue) – Decimal Number (18,4)
• Unit Price Currency (cre05_unitpricecurrency) – Text field
• Unit Price Unit (cre05_unitpriceunit) – Text field
• Quantity (tons) (cre05_quantitytons) – Decimal Number (18,4)
• Incoterms (cre05_incoterms) – Text field
• Location (cre05_location) – Text (Single line)
• Delivery Time (days) (cre05_deliverytimedays) – Whole Number
• Payment Terms (cre05_paymenttermstext) – Multiple Lines of Text
• Notes (cre05_notes) – Multiple Lines of Text
• Confidence (0–1) (cre05_confidence01) – Decimal Number (18,4)
• Quoted Price (€/ton) (cre05_quotedprice_eur_ton) – Decimal Number (18,4)
• Market Price (€/ton) (cre05_marketprice_eur_ton) – Decimal Number (18,4)
• Delta (%) (cre05_deltapercent) – Decimal Number (18,6)
• Received At (cre05_receivedat) – Date & Time (Time-zone)
• Email ID (cre05_emailid) – Text (Single line)
• Raw JSON (cre05_rawjson) – Multiple Lines of Text

Fourth step – add the quotations into Dataverse

We continue adding actions within the Apply to each loop. For each quotation retrieved from the email the supplier sends, we need to get each supplier quotation with market reference data before inserting it into Dataverse.

List rows – CommodityPrices
Retrieves the latest market price for the commodity that matches the symbol extracted from the email.

Filter: nn_symbol eq @{outputs('Compose_cmpMarketSymbol')}
Order by: nn_collectedat desc
Row count: 1

This ensures we get only the most recent market record.

Compose cmpMarketRow
Selects the first record from the List rows output.

first(body('List_rows')?['value'])

Compose cmpMarketPrice
Extracts the normalized market price in €/ton.

outputs('Compose_cmpMarketRow')?['nn_priceeurperton']

Compose cmpDeltaPct
Calculates the % difference between the supplier’s quoted price and the market price.

if(
  and(not(empty(string(outputs('Compose_cmpMarketPrice')))), greater(float(outputs('Compose_cmpMarketPrice')), 0)),
  div(
    sub(float(outputs('Compose_cmpPriceEURperTon')), float(outputs('Compose_cmpMarketPrice'))),
    float(outputs('Compose_cmpMarketPrice'))
  ),
  null
)

Add a new row – SupplierQuotations
Inserts a new record into the Dataverse table SupplierQuotations with all normalized fields:

• Supplier name & email
• Product & symbol
• Quantity, unit, incoterms, location
• Quoted price (€/ton), market price, delta %
• Additional info (payment terms, notes, JSON, timestamp)

Fifth Step – test the automation process with a sample email

Here’s a sample email we can use to test this flow:

Subject: UCO & B100 Weekly Offer – CIF Aveiro

Dear Procurement Team,

Please find below our latest offer:

Product
Quantity
Unit Price
Terms
Delivery Location

UCO
550 MT
€940 per ton
FOB
Sines

Biodiesel (B100)
400 MT
€1,070 per ton
CIF
Aveiro

Best regards,
Laura Schmidt
GreenFuel Europe
laura.schmidt@greenfuel.eu

Let’s try it out and check the flow running, the comparison of prices with our commodity prices database and calculate the delta!

The flow automatically extracted both offers, enriched them with market reference data from the CommodityPrices table, and created the corresponding records in Dataverse (SupplierQuotations).

Field	UCO (Row 1)	Biodiesel (B100) (Row 2)
SupplierName	GreenFuel Europe	GreenFuel Europe
SupplierEmail	laura.schmidt@greenfuel.eu	laura.schmidt@greenfuel.eu
ProductText	UCO	Biodiesel (B100)
Symbol	UCO	B100
QuantityTons	550	400
UnitPriceValue (Quoted)	€940.00	€1,070.00
UnitPriceCurrency	EUR	EUR
UnitPriceUnit	per ton	per ton
MarketPriceEURperTon	€338.02	€876.13
DeltaPercent	1.7809 → +178.1 %	0.2213 → +22.1 %
Incoterms	FOB	CIF
Location	Sines	Aveiro
PaymentTermsText	30 days after delivery	30 days after delivery
Notes	Valid until: 05 Nov 2025	Valid until: 05 Nov 2025
Confidence_0_1	0.95	0.95
ReceivedAt	29/10/2025 18:05	29/10/2025 18:05

Each quotation line from the email was converted into a Dataverse record, with all contextual and calculated data correctly populated:

• SupplierName / SupplierEmail were extracted from the signature.
• MarketPriceEURperTon and DeltaPercent were calculated accurately.
• Notes and PaymentTermsText were preserved.
• Confidence_0_1 = 0.95, confirming a high parsing confidence from the prompt.

Final Step – Time for fun

By the end of this automation journey, we transformed a repetitive, error-prone process — reading and comparing supplier quotations — into a fully automated, intelligent workflow.

Every new email containing commodity offers is now parsed by AI, structured into Dataverse tables, and immediately cross-checked against the latest market prices.

This saves hours of manual work, eliminates copy-paste errors, and allows the procurement team to focus on negotiation and strategy rather than administration.

The results speak for themselves: within seconds, each quotation is extracted, validated, and enriched with market data — ready for dashboards, alerts, or approval workflows.

What Could Be Done Next

This flow is just the foundation.

Several enhancements can take it even further:

• Automatic alerts and scoring – send Teams or email notifications when a supplier price exceeds market benchmarks by more than a set threshold.
• Power BI integration – visualize historical trends, supplier performance, and cost deltas directly in dashboards connected to Dataverse.
• Supplier database enrichment – automatically create or update supplier profiles when new senders appear.
• AI feedback loop – use each new email to continuously retrain or fine-tune the extraction prompt, improving accuracy over time.
• Contract automation – trigger document generation (Word/PDF) when a quotation is approved, completing the end-to-end sourcing process.