Power BI Integration

Connect OilPriceAPI to Microsoft Power BI for real-time oil price dashboards, automated commodity reports, and energy market analytics.

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:

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

Step-by-Step Integration

Step 1: Get Your API Key

  1. Log in to your OilPriceAPI Dashboard
  2. Navigate to API Keys section
  3. Create a new key with a descriptive name (e.g., "Power BI Integration")
  4. 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

  1. Open Power BI Desktop
  2. Click Get Data > Web
  3. Select Advanced option
  4. Configure the connection:

URL parts:

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

HTTP request header parameters:

NameValue
AuthorizationToken YOUR_API_KEY
  1. Click OK to connect

[Screenshot: Power BI Advanced Web connector configuration]

Step 3: Transform the Data

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

  1. Click To Table to convert the record to a table
  2. Expand the data column
  3. Select the columns you need: price, formatted, code, created_at
  4. Set appropriate data types:
    • price: Decimal Number
    • created_at: DateTime
    • code: Text
  5. 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:

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

[Screenshot: Example Power BI dashboard with WTI and Brent crude price trends]

Step 6: Configure Automatic Refresh

Set up scheduled refresh to keep your dashboard current:

  1. Publish your report to Power BI Service
  2. Go to Dataset settings
  3. Expand Data source credentials
  4. Click Edit credentials and enter your API key
  5. Under Scheduled refresh, enable automatic updates
  6. 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:

  1. Create a new Parameter named OilPriceAPIKey
  2. Set it as type Text with Required option
  3. Reference in your query: Headers = [Authorization = "Token " & OilPriceAPIKey]
  4. 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

PlanRequests/MonthRecommended Refresh
Free1,0001x per day
Hobby10,0004x per day
Professional100,000Every 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

FAQ

Can I use Power BI with the free OilPriceAPI plan?

Yes, the free plan includes 1,000 requests per month, which is sufficient for daily dashboard updates with a few commodities. For more frequent updates or additional commodities, consider upgrading to a paid plan.

How often does OilPriceAPI data update?

Commodity prices update every 5-15 minutes during market hours. For real-time streaming updates, see our WebSocket documentation.

Can I combine OilPriceAPI data with other data sources in Power BI?

Absolutely. Power BI excels at combining multiple data sources. You can join oil price data with your internal sales data, inventory systems, or other APIs to create comprehensive energy analytics dashboards.

What commodities are available?

OilPriceAPI provides 220+ commodities including crude oil benchmarks (WTI, Brent, Dubai), natural gas, refined products, and more. See the full Commodities List.

Is there a Power BI template available?

We recommend starting with the examples in this guide. For enterprise customers, custom template development is available - contact [email protected].

Last Updated: