How to Combine Two Salesforce Data Sources in Power BI
Question details
The user needs to consolidate data from two different Salesforce organizations into a single dataset within Power BI.
- Product
- Power BI
- Device & OS
- not provided
- Scenario
- Connecting and transforming data from multiple Salesforce environments for unified reporting.
- Observed behavior
- The goal is to successfully merge or append tables from both Salesforce connections into a cohesive data model using Power Query.
Ensure you have active login credentials and API access for both Salesforce organizations, and that Power BI Desktop is updated to the latest version.
Combine Salesforce Organizations Using Power Query
Connect to each Salesforce organization separately, then use Append or Merge in Power Query to consolidate the tables into a single dataset.
To combine data from multiple Salesforce sources, you must establish independent connections to each organization. Once connected, Power Query allows you to align and stack the data structures or join related records across the organizations.
Open Power BI Desktop. Go to 'Get Data' > 'More...', select 'Salesforce Objects' or 'Salesforce Reports', and sign in using the credentials for your first Salesforce organization. Load the required tables.
Repeat the 'Get Data' process for the second Salesforce organization, ensuring you authenticate with its distinct login credentials. Load the necessary tables into Power BI.
Click 'Transform Data' on the Home ribbon to open the Power Query Editor where all your loaded Salesforce tables will be listed.
Select one of the tables. On the Home tab, click 'Append Queries' if you want to stack identical tables (e.g., combining Leads from both orgs), or select 'Merge Queries' if you need to join related records based on a common key.
Ensure that the column names, fields, and data types match across the combined queries. Once verified, click 'Close & Apply' to load the unified dataset into your Power BI data model.
Manage and Analyze Exported Salesforce Data with WPS Office
While complex multi-org data modeling requires specialized tools like Power BI, WPS Office provides a lightweight, highly compatible spreadsheet solution for viewing, filtering, and analyzing exported Salesforce reports without the heavy resource usage.
- 1. Export Data: Export your combined datasets or reports from Salesforce or Power BI as a .csv or .xlsx file.
- 2. Open with WPS Spreadsheet: Launch WPS Office, select 'Spreadsheet', and open your downloaded file seamlessly.
- 3. Analyze and Visualize: Use WPS Spreadsheet's built-in PivotTables, charts, and filtering tools to perform quick data analysis.
Frequently Asked Questions
What is the difference between Merge and Append in Power Query?
Append Queries stacks rows from one table onto the bottom of another (useful for combining identical tables from different orgs), while Merge Queries joins columns from two tables horizontally based on matching key values.
Why do I get errors when appending two Salesforce tables?
Errors typically occur if the column names or data types do not exactly match between the two Salesforce organizations. You must rename columns and adjust data types in Power Query so they align perfectly before appending.
Can I schedule a refresh for combined datasets from different Salesforce orgs?
Yes. Once published to the Power BI Service, you can configure a scheduled refresh. You will need to ensure that the credentials for both Salesforce organizations are correctly authenticated under the dataset's Data Source Credentials settings.
