Web connector 

The fastest way to integrate Power BI with Fastmarkets API is using the Web connector. It supports Rest APIs, allows custom headers and works with Power Query (M language).

Typical pattern: 

Power Query Execution Constraints

When designing authentication in Power BI, it is important to understand how Power Query executes.

These constraints may affect the authentication approach. If token generation is embedded directly inside multiple queries, Power Bi may request multiple tokens unintentionally and exceed Fastmarkets' authentication limits. This could cause scheduled updates to fail.

Best Practices for Authentication in Power BI

• Centralize token generation
  Create one query responsible for authentication. Reuse the token across all API requests
  This ensures token reuse and stable refresh behavior.
   
• Use Power BI parameters for credentials
  Store serviceName, and serviceKey as Power BI parameters. Avoid hard‑coding credentials in multiple places
   
• Design for token expiry
  Expect refresh failures if a token becomes invalid. Ensure authentication logic runs once per refresh cycle

Security Considerations: Using Power Query Parameters

Storing access credentials in Power Query parameters is not the most secure approach. While it works, the credentials are still stored inside the Power BI model, which can cause security and access issues after the dataset is published to the Power BI Service.

For the use cases when Power BI is used by small teams of individual analysts the parameter‑based approach described in this article is usually acceptable, as long as dataset access is tightly controlled. Make sure that access to the dataset is restricted and credentials are managed carefully.

How to Connect Power BI to Fastmarkets APIs?

Step 1 - Open Power Query Editor

Use Power BI Desktop. Open Get Data → Blank Query.

Step 2 - Create Power BI Parameters

Create the following Power BI parameters (Manage Parameters → New):

Step 3 - Create an Authentication Query

Create a new blank query named

Fastmarkets_Authenticate

Power Query (M): Authentication

let
    AuthUrl = "https://auth.fastmarkets.com/connect/token",

    Body =
        [
            grant_type  = "servicekey",
            client_id  = "service_client",
            serviceName = FM_ServiceName,
            serviceKey  = FM_ServiceKey,
            scope       = FM_Scope
        ],

    Response =
        Json.Document(
            Web.Contents(
                AuthUrl,
                [
                    Content = Text.ToBinary(Uri.BuildQueryString(Body)),
                    Headers = [#"Content-Type" = "application/x-www-form-urlencoded"]
                ]
            )
        ),

    AccessToken = Response[access_token]
in
    AccessToken

Step 4 - Create a Reusable API Request Function

Create a function query named:

Fastmarkets_ApiRequest

Power Query (M):  data request function

(ApiUrl as text, Token as text, optional QueryParams as nullable record) as any =>
let
    Options =
        [
            Headers =
                [
                    Authorization = "Bearer " & Token,
                    Accept = "application/json"
                ],
            Query = if QueryParams = null then [] else QueryParams
        ],

    Response =
        Json.Document(
            Web.Contents(ApiUrl, Options)
        )
in
    Response

Step 5 - Call the function from a regular query

Create a function query named:

Fastmarkets_GetLatestPrice

let
    Token = Fastmarkets_Authenticate,

    ApiUrl =
        "https://api.fastmarkets.com/physical/v2/Prices/None",

    Response =
        Fastmarkets_ApiRequest(
            ApiUrl,
            Token,
            [
                symbols = "MB-STE-0009,MB-STE-0014"
            ]
        ),

    Data = Response[instruments],
    Table = Table.FromRecords(Data),
    pricesTable = Table.ExpandRecordColumn(
            Table.ExpandListColumn(Table, "prices"),
            "prices",
            {"assessmentDate", "low", "high", "revision"},
            {"assessmentDate", "low", "high", "revision"}
        )

in
    pricesTable

Authentication & Data Query

let
    //authentication
     AuthUrl = "https://auth.fastmarkets.com/connect/token",

    Body =
        [
            grant_type  = "servicekey",
            client_id  = "service_client",
            serviceName = FM_ServiceName,
            serviceKey  = FM_ServiceKey,
            scope       = FM_Scope
        ],

    Response =
        Json.Document(
            Web.Contents(
                AuthUrl,
                [
                    Content = Text.ToBinary(Uri.BuildQueryString(Body)),
                    Headers = [#"Content-Type" = "application/x-www-form-urlencoded"]
                ]
            )
        ),

    AccessToken = Response[access_token],

    //data request

    ApiUrl =
        "https://api.fastmarkets.com/physical/v2/Prices/None",

    Response =
        Fastmarkets_ApiRequest(
            ApiUrl,
            AccessToken,
            [
                symbols = "MB-STE-0009,MB-STE-0014"
            ]
        ),

    Data = Response[instruments],
    Table = Table.FromRecords(Data),
    pricesTable = Table.ExpandRecordColumn(
            Table.ExpandListColumn(Table, "prices"),
            "prices",
            {"assessmentDate", "low", "high", "revision"},
            {"assessmentDate", "low", "high", "revision"}
        )

in
    pricesTable