Recommendation to report on Salesforce historical data

Hi Lucy_Lim I know this reply is very late, but were you able to find a solution? I've used the Salesforce connector but I had some issues updating my data too. As a workaround, maybe you can try to test your connection with a 3rd party connector. I've tried windsor.ai, supermetrics and funnel.io. I stayed with windsor because it is much cheaper so just to let you know other options. In case you wonder, to make the connection first search for the Salesforce connector in the data sources list:

After that, just grant access to your Salesforce account using your credentials, then on preview and destination page you will see a preview of your Salesforce fields:

There just select the fields you need. It is also compatible with custom fields and custom objects, so you'll be able to export them through windsor.  Finally, just select PBI as your data destination and finally just copy and paste the url on PBI --> Get Data --> Web --> Paste the url.

Hi Lucy_Lim 

For reporting on Salesforce historical data in Power BI, you might want to try an alternative connection method - Power BI Connector for Salesforce on AppExchange. It's a native Salesforce app installed inside your org, which makes pulling history objects (like Opportunity History, Field History, etc.) and large historical datasets more manageable:

Why it might fit:

• Server-side filtering on historical objects - filter by date range before data leaves Salesforce, so you only pull the periods you need
• No 2,000-row limit on reports - relevant when historical objects accumulate large volumes
• Supports custom fields, custom objects, and joined reports
• 30-day free trial
• Setup docs: https://metricasoftware.com/docs/salesforce/
• Responsive support if you get stuck: https://metricasoftware.com/docs/salesforce/contact-support/

Happy to set up a quick demo if it'd help. Just reach out via the support link.

Cheers,

Metrica Team.

Hi Lucy_Lim 

The reason you are hitting that gap in your current approach is that Power BI is structurally designed to be a data-rendering tool rather than a state-retaining database; every single time your report undergoes a refresh, Power Query completely wipes its existing cache and evaluates the raw M-code instructions from a fresh slate, meaning your temporary "MONTH1" append query evaporates because it cannot write data backwards into your static 3YearData CSV file. Since you noticed that the Salesforce Reports API truncates your extractions at a hard 2,000-row limit, you should completely abandon the "Reports" folder within the connector window and instead connect directly to the raw Salesforce Objects layer (such as the Opportunity, Account, or Contact backend tables), which uses the high-scale Salesforce SOAP/REST API and completely bypasses that 2,000-row truncation limit so you can pull hundreds of thousands of historical rows natively. Once you point your query to the base Objects rather than a pre-built Salesforce user report, you can easily implement Power BI Incremental Refresh directly inside the Service by setting up a RangeStart and RangeEnd datetime parameter to automatically freeze your historical years in independent cloud partitions while only scanning Salesforce for the current month's changes during scheduled updates. If you choose not to use the native Object connector and prefer a completely bulletproof enterprise architecture, migrating this process to your existing SQL Server database is absolutely the best path forward; you can quickly fix that legacy script to run on a SQL Agent scheduled job that pulls the monthly delta from Salesforce and runs an INSERT INTO command to continuously append new rows onto a master tracking table, leaving Power BI with nothing to do except run a highly optimized, standard import query against a single, reliably growing database source.