Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now
Hey guys,
A few weeks ago I had a problem with the 2000 rows limitation while importing data from Salesforce to Power BI. I did a little research and found out that this was a problem with the Salesforce API, Microsoft couldn't do anything to fix it and a lot of people were struggling with that issue as well.
However, I finally found an easy and quick way to bypass this problem and since this community helped me a lot when I started working with Power BI, I feel that now is my time to give it back. Now we don't have to manually download the report from Salesforce and replace an Excel file or create a lot of reports to split the data in pieces of 2000 rows.
You will import data from Salesforce reports to a Google Sheets and then import data from this spreadsheet to your Power BI file.
- Open a sheet in Google Sheets.
- Go to Add-Ons > Get Add-Ons.
- Search for 'Salesforce' and install the 'Data Connector for Salesforce'.
- Login to your Salesforce account, allow and authorize the permissions Google Sheets needs to access the data.
- Go to Add-ons > Data Connector for Salesforce > Open.
- You will find this menu:
- Now you can import data from all your reports with no limitation clicking on "Reports".
- After that you can click on "Refresh" and schedule auto data refreshes every 4, 8 or 24 hours.
- Open your Power BI, Get Data > From Web
- Get a shareable link of your Google Sheet (with permission to view at least) and paste it.
- Modify your Google Sheet link from:
https://docs.google.com/spreadsheets/d/google-sheet-example/edit?usp=sharing
To:
https://docs.google.com/spreadsheets/d/google-sheet-example/export?format=xlsx&id=google-sheet-example
Now it's done. You can start working with your Salesforce data without the 2000 rows limitation and with auto refreshes.
Hope it helps!
Solved! Go to Solution.
Hi @fernandohiltner ,
That's pretty cool! Thanks for sharing that!
You can upvote the request to increase that 2000-row limitation. The more votes, the more likely it will be changed. Here is that link. https://ideas.salesforce.com/s/idea/a0B8W00000GdYvXUAV/power-bi-communicate-with-microsoft-developme...
I don't think this is working anymore. The connector will also download only 2000 records into the gsheet.
Then it will tell you to re-auth to get "all data" but it just does not work. You can be revoking in SF and authorizing here in G Sheet over and over this message will just keep popping out and of course, the connector continues to download only 2000 records:
(Not to mention the message is outdated too - you need to revoke the permissions in your personal settings in SF).
And of course, no way to contact Google about this (maker of the add-on).
Just want to say thank you for this neat workaround, awesome stuff!👍
My Salesforce object has 137 columns, when trying to extract it gives the following error: Error: Limit Exceeded: URL Length of URLFetch.
Does this method allow authentication using username/password or another method or it is only via unsecured web connector (Anyone with the link can access the data)?
Hi Fernando,
Thanks for the solution here, is was fantastic.
But unfortunately I still have an issue to solve, when I am using google sheets and as my report is quite long, the Salesforce connector shows un error "Exceeded maximum execution time", and because of that I can't import the report. I have searched about this error and everyone says the limits is 5 min running, but I don't have a solution for that. Could you please help me on it?
Thanks,
MatheusFF
Hi Matheus!
@Anonymous
Firstly, thank you for your feedback.
Regarding your problem, I have never faced this issue before. I guess this must be a limitation within Google Sheets for running scripts. I guess I would split the report into smaller parts (to run the request in less than 5 minutes) and then aggregate it all inside Power BI. Is this a viable option for you?
Hope it helps!
Hi @fernandohiltner ,
That's pretty cool! Thanks for sharing that!
Check out the October 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.
User | Count |
---|---|
67 | |
61 | |
23 | |
17 | |
12 |