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

[kushanNa]

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.