How to Connect Salesforce to Power BI

Connecting your Salesforce data directly to Power BI opens up a powerful new world of visualization and analysis. Doing so allows you to blend your CRM data with other sources, build highly interactive dashboards, and share insights far beyond what’s possible with Salesforce’s native reports. This article will guide you through the process step-by-step, from initial connection to building your first visual.

Why Connect Salesforce to Power BI?

While Salesforce has solid built-in reporting, Power BI takes your data analysis to a completely different level. Instead of being confined to the standard dashboard components within Salesforce, you gain the full capabilities of Microsoft’s business intelligence platform. The primary benefits come down to flexibility, depth, and the ability to see the bigger picture.

Consider these advantages:

• Advanced Visualizations: Power BI offers a massive library of charts, graphs, tables, and maps that go well beyond Salesforce's options. You can create completely custom visuals that tell a more compelling story about your sales pipeline, team performance, or customer behavior.
• Data Mashups: Your business doesn't run just on Salesforce. You can combine your Salesforce data with information from other sources like Google Analytics, financial data in Excel, marketing automation platforms, and more. This lets you analyze the entire customer journey, from ad click to closed-won deal, all in one place.
• Powerful Calculations with DAX: Data Analysis Expressions (DAX) is Power BI’s formula language. It allows you to create sophisticated calculations and custom metrics that are difficult or impossible to build in Salesforce. Think complex year-over-year comparisons, cohort analysis, or lead-to-opportunity velocity metrics.
• Seamless Sharing and Collaboration: Distributing insights to stakeholders who don't have (or need) a Salesforce license is much easier with Power BI. You can publish reports and dashboards to the Power BI service, which can be shared securely with anyone inside or outside your company.

Before You Begin: What You'll Need

Before jumping into the connection process, taking a moment to ensure you have a few things in order will save you a lot of troubleshooting time later.

1. Power BI Desktop

All the data connection and report building happens in Power BI Desktop. It’s a free application for Windows that you can download directly from Microsoft’s website. If you don't have it installed already, make that your first step.

2. Salesforce Edition & API Access

Power BI connects to Salesforce using its API (Application Programming Interface). This capability is only available in certain Salesforce editions. You need one of the following:

• Professional*
• Enterprise
• Unlimited
• Developer

*Note: For the Professional edition, API access is not included by default and must be added to your plan. If you are on the Professional edition and experience connection issues, check with your Salesforce admin to see if API access is enabled.

3. Your Salesforce Credentials

You’ll need your standard Salesforce username and password to authenticate the connection. Depending on your organization's security settings, you may also need a security token. Salesforce requires a security token for logins from outside your company’s trusted IP ranges. If you get a login error, you can get your security token by resetting it in your personal Salesforce settings, it will be emailed to you.

A Step-by-Step Guide to Connecting Power BI and Salesforce

With the prerequisites out of the way, you’re ready to establish the connection. The process is straightforward and handled almost entirely within the Power BI interface.

Step 1: Locate the Salesforce Connector in Power BI

First, open a blank report in Power BI Desktop. In the Home ribbon at the top of the window, click on the Get Data button. This will open a window with a long list of all the native data connectors available in Power BI.

You can either type "Salesforce" into the search bar or find it by navigating to the Online Services category. You'll see two options: Salesforce Objects and Salesforce Reports. It's important to understand the difference between the two.

Step 2: Choose Your Connection Type

Which option you choose next depends entirely on how you want to work with your data and what you want to accomplish.

Connecting with "Salesforce Objects"

This is the most powerful and flexible option. It gives you direct access to the raw data tables (called "Objects") in your Salesforce instance, like Account, Opportunity, Lead, User, and Case. This method is ideal if you want to:

• Build a comprehensive data model from scratch.
• Create complex relationships between different tables (e.g., analyzing opportunities by account type).
• Drill down into highly specific data points that aren't included in your standard reports.

Connecting with "Salesforce Reports"

This option is much simpler and is perfect for beginners or users who already have Salesforce reports that contain exactly what they need. Instead of connecting to raw tables, Power BI will simply import the data from a report you’ve already created in Salesforce. This is a great choice if you:

• Want to visualize an existing sales pipeline or activity report quickly.
• Are less comfortable with data modeling and relationships.
• Don't need to blend data tables and just want a better visual representation of a specific report.

For this walkthrough, we'll choose Salesforce Objects, as it’s the most common and versatile use case. Select it and click Connect.

Step 3: Authenticate and Sign In

Next, Power BI will ask you for the URL to connect to. You’ll usually choose the Production URL to connect to your live Salesforce org or Custom if you're connecting to a sandbox environment.

After you click OK, a Salesforce login window will pop up. Enter your username and password to authenticate. If your network isn't configured as a trusted IP range, you'll need to append your security token to the end of your password here.

Step 4: Select and Load Your Data

Once you’ve successfully signed in, the Navigator window will appear. This is where you’ll see a list of every available object in your Salesforce org. Don't be overwhelmed by the long list!

Start small. For a basic sales pipeline report, check the boxes next to the objects you need, such as:

• Opportunity (for deal amounts, stages, and close dates)
• Account (for customer names, industries, and locations)
• User (to analyze performance by sales representative)

As you select an object, you’ll see a preview of the data on the right. Once you've selected your objects, you have two options at the bottom of the window: Load or Transform Data.

Always choose Transform Data. This opens the Power Query Editor, an incredibly powerful tool for cleaning and preparing your data before it gets loaded into your report. 'Loading' directly will often result in a cluttered and difficult-to-use dataset.

A Quick Intro to Transforming Data in Power Query

Power Query is where you can shape your Salesforce data into a lean, useful format. For new users, focus on these simple but crucial actions:

• Remove Unnecessary Columns: Your Salesforce objects will likely have hundreds of columns, most of which you won't need for your report. To improve performance and make your dataset easier to navigate, select the columns you don't want, right-click, and choose "Remove Columns."
• Filter Rows: You may not want every single opportunity from the last decade. Use the filter buttons at the top of the columns (such as the CreatedDate or StageName column) to narrow down the data to a relevant subset, like "deals from the last year that are not Closed Lost."
• Check Data Types: Power Query is pretty good at guessing data types, but it's always smart to double-check. Ensure that date columns have a 'Date/Time' format, numerical fields like Amount have a 'Decimal Number' format, and so on. The data type icon is visible at the top-left of each column header.

Once you are happy with the state of your data, click the Close & Apply button in the top left corner. Power BI will then load your prepared data into the actual report model.

Building Your First Power BI Visual with Salesforce Data

Now for the fun part. With your data loaded, you're ready to create a visualization. In the main report view, look at the Data pane on the right-hand side. You'll see the tables you loaded (Opportunity, Account, etc.) with all their fields.

Let's create a simple bar chart of Opportunity Amount by Stage:

1. From the Visualizations pane, click on the icon for a 'Stacked column chart.' A blank visual will appear on your report canvas.
2. In the Data pane, find the Opportunity table. Click and drag the StageName field onto the 'X-axis' box in the Visualizations pane.
3. Next, drag the Amount field from the same table and drop it into the 'Y-axis' box.

And that's it! Power BI automatically generates an interactive chart summarizing your sales pipeline. You can now resize it, change the colors, add filters, and continue building out your dashboard with more visuals.

Final Thoughts

Connecting Salesforce to Power BI lets you step outside the boundaries of built-in CRM reporting and build truly dynamic analytical tools. By combining and transforming your data, you can uncover insights that were previously hidden, helping you make smarter, data-driven decisions about your sales process.

Once you've tasted the power of consolidated data, you might wonder how to make it even easier. We've found many businesses that, even after connecting platforms like Salesforce, are still stuck manually moving data between Shopify, Google Analytics, their ad platforms, and spreadsheets. Our team built Graphed to simplify this entire process. We connect to all your marketing and sales sources in one click, letting you create dashboards and ask questions using simple, natural language. Instead of wrangling formulas and schemas, you can get real-time answers in seconds.