Recommendation to report on Salesforce historical data

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.