Extracting Salesforce Data in Power BI Using Client Credentials Flow (External Client App Manager)

Salesforce provides several OAuth 2.0 authentication flows to securely access data. One of the most suitable flows for server-to-server integrations and automation tools like Power BI is the Client Credentials Flow.

In this blog, we will cover:

 

1. Salesforce External Client App Manager configuration (required setup)
2. Why Client Credentials Flow is used
3. Power BI M code walkthrough
4. Best practices & security note.

 

✅ When to Use Client Credentials Flow

 

Use Client Credentials Flow when:

 

• No Salesforce user interaction is required
• You are building automated integrations
• You only need API access, not UI access

This is ideal for:

• Power BI
• Backend services

 

🔧 Step 1: Salesforce Configuration (Connected App)

 

1️⃣ Create a Connected App

1. Log in to Salesforce
2. Go to Setup
3. Search for External Client App Manager
4. Click New External Client App

 

2️⃣ Basic Information

Fill in:

• App Name
• API Name
• Contact Email

 

3️⃣ Enable OAuth Settings

✔ Check Enable OAuth Settings

Set:

• Callback URL
  (Required but not used in client credentials)

https://localhost.com

 

4️⃣ OAuth Scopes

Add required scopes:

• I have added all the scopes for this test but you can only pick the once that you need

 

5️⃣ Enable Client Credentials Flow

In OAuth Settings:

• ✔ Enable Client Credentials Flow – (In both pages Policies and settings)
• Assign a Run As User -This user controls data access (I have added the system admin for this example but you can add a spesific user that you need with the required permission to do changes to the app )

 

6️⃣ Save and Capture Credentials

After saving, copy:

• Consumer Key → client_id
• Consumer Secret → client_secret

From the App settings page.

 

🌐 Step 2: Salesforce Domain

 

Your Salesforce base URL looks like:

https://<your-org>.my.salesforce.com

 

We will reuse one base URL for:

• Token generation
• API calls

 

⚙️ Step 3: Power BI M Code (Client Credentials Flow)

Below is the complete Power Query (M) script.

 

🧠 What This Code Does

1. Uses a single Salesforce base URL
2. Generates an access token
3. Executes a SOQL query
4. Transforms JSON into a table

 

🧩 Full Power BI M Code

 

let
    // --------------------------------------------------
    // 1. Base Salesforce Domain (SINGLE data source)
    // --------------------------------------------------
    BaseUrl =
        "https://xxxxxxxxxxxxxdevelop.my.salesforce.com",

    // --------------------------------------------------
    // 2. Generate Access Token
    // --------------------------------------------------
    TokenResponse =
        Json.Document(
            Web.Contents(
                BaseUrl,
                [
                    RelativePath = "services/oauth2/token",
                    Content =
                        Text.ToBinary(
                            Uri.BuildQueryString(
                                [
                                    grant_type = "client_credentials",
                                    client_id =
                                        "xxxxxxxxxxxxxxxx",
                                    client_secret =
                                        "xxxxxxxxxxxxxxxx"
                                ]
                            )
                        ),
                    Headers = [
                        #"Content-Type" = "application/x-www-form-urlencoded"
                    ]
                ]
            )
        ),

    AccessToken = TokenResponse[access_token],

    // --------------------------------------------------
    // 3. SOQL Query
    // --------------------------------------------------
    SoqlQuery =
        "SELECT Id, Subject, StartDateTime, EndDateTime, Location FROM Event LIMIT 10",

    // --------------------------------------------------
    // 4. Call Salesforce REST API (same BaseUrl)
    // --------------------------------------------------
    ApiResponse =
        Json.Document(
            Web.Contents(
                BaseUrl,
                [
                    RelativePath = "services/data/v65.0/query/",
                    Query = [
                        q = SoqlQuery
                    ],
                    Headers = [
                        Authorization = "Bearer " & AccessToken,
                        #"Content-Type" = "application/json"
                    ]
                ]
            )
        ),

    // --------------------------------------------------
    // 5. Transform Response
    // --------------------------------------------------
    Records = ApiResponse[records],

    TableFromRecords =
        Table.FromList(
            Records,
            Splitter.SplitByNothing(),
            null,
            null,
            ExtraValues.Error
        ),

    ExpandedTable =
        Table.ExpandRecordColumn(
            TableFromRecords,
            "Column1",
            {
                "Id",
                "Subject",
                "StartDateTime",
                "EndDateTime",
                "Location"
            }
        )
in
    ExpandedTable

 

📊 Output in Power BI

 

Authentication method - Anonymous 

Privacy level setting for this data source - Organizational 

 

This produces a clean table with:

Id	Subject	StartDateTime	EndDateTime	Location

 

 

You can now:

• Refresh automatically
• Build dashboards
• Publish to Power BI Service

 

🚀 Why This Pattern Works Well in Power BI

 

• ✅ Single data source (avoids credential issues)
• ✅ No user login required
• ✅ Scales well for enterprise usage

 

🧾 Final Thoughts

 

Using Salesforce Client Credentials Flow with Power BI is a clean, secure, and production-ready approach for enterprise reporting. With a properly configured External App and a single base URL strategy, you can reliably extract Salesforce data without user intervention.