Power BI Integration

Connect OilPriceAPI to Microsoft Power BI for real-time oil price dashboards, automated commodity reports, and energy market analytics. Perfect for commodities trading analysis, fleet management reporting, and logistics cost tracking.

Overview

Power BI is a powerful business intelligence platform that enables you to create interactive visualizations and share insights across your organization. By integrating OilPriceAPI with Power BI, you can build dynamic dashboards that automatically update with the latest crude oil, natural gas, and commodity prices.

Key Benefits:

Real-time oil price data in Power BI dashboards
Automated daily/weekly price reports
Historical trend analysis with interactive charts
Share commodity insights across your organization
Combine energy prices with your internal business data

Prerequisites

Before you begin, ensure you have:

Power BI Desktop installed (free download from Microsoft)
OilPriceAPI account with an active API key (Sign up)
Basic familiarity with Power Query

Step-by-Step Integration

Step 1: Get Your API Key

Log in to your OilPriceAPI Dashboard
Navigate to API Keys section
Create a new key with a descriptive name (e.g., "Power BI Integration")
Copy your API key - you'll need it in the next steps

For detailed authentication setup, see our Authentication Guide.

Step 2: Connect Power BI to OilPriceAPI

Open Power BI Desktop
Click Get Data > Web
Select Advanced option
Configure the connection:

URL parts:

https://api.oilpriceapi.com/v1/prices/latest?by_code=WTI_USD,BRENT_CRUDE_USD

HTTP request header parameters:

Name	Value
Authorization	Token YOUR_API_KEY

Click OK to connect

Step 3: Transform the Data

Power Query Editor will open with your API response. Transform the JSON data:

Click To Table to convert the record to a table
Expand the data column
Select the columns you need: price, formatted, code, created_at
Set appropriate data types:
- price: Decimal Number
- created_at: DateTime
- code: Text
Rename columns for clarity

Power Query M Code:

let
    Source = Json.Document(
        Web.Contents(
            "https://api.oilpriceapi.com/v1/prices/latest",
            [
                Query = [by_code = "WTI_USD,BRENT_CRUDE_USD,NATURAL_GAS_USD"],
                Headers = [Authorization = "Token YOUR_API_KEY"]
            ]
        )
    ),
    Data = Source[data],
    ToTable = Table.FromRecords({Data}),
    ExpandedData = Table.ExpandRecordColumn(ToTable, "Column1", {"price", "formatted", "code", "created_at", "currency"}),
    TypedColumns = Table.TransformColumnTypes(ExpandedData, {
        {"price", type number},
        {"created_at", type datetime},
        {"code", type text},
        {"currency", type text}
    })
in
    TypedColumns

Step 4: Load Historical Data

For trend analysis, connect to historical endpoints:

let
    Source = Json.Document(
        Web.Contents(
            "https://api.oilpriceapi.com/v1/prices/past_month",
            [
                Query = [by_code = "WTI_USD", interval = "1d"],
                Headers = [Authorization = "Token YOUR_API_KEY"]
            ]
        )
    ),
    Prices = Source[data][prices],
    ToTable = Table.FromList(Prices, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    ExpandedRecords = Table.ExpandRecordColumn(ToTable, "Column1", {"price", "created_at", "code", "type"}),
    TypedData = Table.TransformColumnTypes(ExpandedRecords, {
        {"price", type number},
        {"created_at", type datetime}
    })
in
    TypedData

See our Historical Prices API for available time ranges and intervals.

Step 5: Create Visualizations

With your data loaded, create compelling visualizations:

Line Chart - Show price trends over time
Card Visual - Display current prices prominently
Table - Compare multiple commodity prices
KPI Visual - Track price changes vs. previous period

Step 6: Configure Automatic Refresh

Set up scheduled refresh to keep your dashboard current:

Publish your report to Power BI Service
Go to Dataset settings
Expand Data source credentials
Click Edit credentials and enter your API key
Under Scheduled refresh, enable automatic updates
Set refresh frequency (recommended: every 1-4 hours)

Advanced Configuration

Multiple Commodities Dashboard

Track all major energy commodities in one dashboard:

let
    Commodities = {"WTI_USD", "BRENT_CRUDE_USD", "NATURAL_GAS_USD", "HEATING_OIL_USD"},
    CommodityList = Text.Combine(Commodities, ","),
    Source = Json.Document(
        Web.Contents(
            "https://api.oilpriceapi.com/v1/prices/latest",
            [
                Query = [by_code = CommodityList],
                Headers = [Authorization = "Token YOUR_API_KEY"]
            ]
        )
    )
in
    Source

Secure API Key Storage

For production deployments, use Power BI parameters to store your API key securely:

Create a new Parameter named OilPriceAPIKey
Set it as type Text with Required option
Reference in your query: Headers = [Authorization = "Token " & OilPriceAPIKey]
Store the actual key in Power BI Service credentials

Error Handling

Add error handling for robust data refresh:

let
    Source = try Json.Document(
        Web.Contents(
            "https://api.oilpriceapi.com/v1/prices/latest",
            [
                Query = [by_code = "WTI_USD"],
                Headers = [Authorization = "Token YOUR_API_KEY"]
            ]
        )
    ) otherwise [status = "error", message = "API request failed"],

    Result = if Source[status] = "success" then Source[data] else null
in
    Result

Rate Limits and Best Practices

Plan	Requests/Month	Recommended Refresh
Free	1,000	1x per day
Hobby	10,000	4x per day
Professional	100,000	Every hour

Best Practices:

Cache data locally in Power BI rather than querying every visualization refresh
Use the interval parameter for historical data to reduce response size
Request only the commodities you need with the by_code parameter
Monitor your usage in the OilPriceAPI Dashboard

For detailed rate limit information, see our Rate Limiting Guide.

Troubleshooting

Authentication Errors

If you see "401 Unauthorized":

Verify your API key is correct and active
Ensure the Authorization header format is Token YOUR_API_KEY (note the space after "Token")
Check that your API key hasn't expired

Data Refresh Fails

If scheduled refresh fails:

Re-enter credentials in Power BI Service dataset settings
Verify your API plan has sufficient requests remaining
Check OilPriceAPI Status for any outages

Performance Issues

For large datasets:

Use the interval parameter to aggregate historical data
Limit date ranges when possible
Enable incremental refresh for historical tables

Frequently Asked Questions

How do I authenticate OilPriceAPI in Power BI?

Authentication in Power BI uses the HTTP Authorization header. When connecting via the Web connector:

Select Advanced mode in the Web connector dialog
Add a header parameter with Name: Authorization and Value: Token YOUR_API_KEY
For scheduled refresh in Power BI Service, re-enter credentials in dataset settings under Data source credentials

For secure key management, store your API key as a Power BI parameter and reference it in your Power Query M code. See the Authentication Guide for detailed setup.

What's the rate limit when using Power BI with OilPriceAPI?

OilPriceAPI limits depend on your plan: Free (1,000/month), Hobby (10,000/month), Starter (50,000/month), Professional (100,000/month). Configure Power BI scheduled refresh to stay within your limits:

Free plan: Refresh once per day maximum
Hobby plan: Refresh up to 4 times per day
Professional plan: Hourly refresh is sustainable

Monitor your usage in the OilPriceAPI Dashboard to avoid exceeding limits.

Can I automate price updates in Power BI?

Yes, Power BI supports automated data refresh:

Publish your report to Power BI Service
Navigate to your dataset settings
Configure Scheduled refresh with your desired frequency
Enter your API credentials in the data source settings

Power BI Pro allows up to 8 daily refreshes; Premium allows up to 48. Match your refresh schedule to your OilPriceAPI plan limits.

What happens if the API call fails in Power BI?

When API calls fail in Power BI:

During development: Power Query shows error details in the editor
During scheduled refresh: The refresh fails and Power BI Service sends an email notification

To handle errors gracefully, wrap your API calls in try/otherwise blocks in Power Query:

let
    Source = try Json.Document(Web.Contents(...))
             otherwise [status = "error", data = null]
in
    Source

Common failure causes include expired API keys, rate limit exceeded, or network issues.

Tableau Integration - Alternative BI tool integration
Looker Integration - Enterprise analytics platform
Google Sheets Integration - Spreadsheet integration
Commodities Trading API - Trading and analytics data
Fleet Management API - Fleet fuel cost tracking
Aviation Fuel API - Airline fuel analytics
Logistics Fuel API - Supply chain cost analysis
Python Developer Guide - Custom code integration
Authentication Guide - API key setup and security
Historical Prices API - Time-series data endpoints
Rate Limiting Guide - Understanding request limits