Power Query & Salesforce Reports: 2000 row limit

In 2014, Microsoft added a Salesforce Reports connector to Power Query making it easy to pull data straight into Excel.  Awesome! Unfortunately, Salesforce limits results to 2000 rows.  That’s not Microsoft’s fault.  That limit was set by Salesforce back in the Middle Ages when resources were scarce.  Or something.  I can’t explain it. It sure does cause a lot of contortions to work around.

Here are three workarounds I’ve used to exceed that 2k limit:

VBA CONTROLLING IE:  Manually exporting a report in the browser is not limited.  So I wrote a VBA macro to fire up IE, log into Salesforce, run a report, click the Export button, download to Excel. Ugh. It works. It’s fragile. It’s a lot of code.

APPEND method: This one’s MUCH easier. Create multiple copies of your report in Salesforce.com, each limited to a different “slice” of the data.  Hopefully, each will be <2000 rows and when combined you’ll have a full dataset. In Power Query, connect to each report, setting all but one (the primary) to “Load To…Only Create Connection“.  Then in the primary query Combine…Append the others and load to the worksheet.  But if any of these queries exceeds 2000 rows, you’ll be missing data and there’s no error message to warn you.

PARAMETER method:  You can run a Salesforce Report in a browser with parameters in the URL. By limiting the report to a small set of data you have a better chance of staying below 2k.  Javier Gonzalez does a good job of explaining this in a blog post here.  Basically, you put filters on your report in Salesforce report ahead of time.  The operator and the value are easy to modify with parameters later.  But the field name is not. In fact, placeholders that allow all data to pass the saved filters might be a good idea.

Example of saved report with filters. Notice that most data is likely to pass through these filters. The unique report ID is used in the URL.
Example URL: https://salesforce.com/00Oa00000XXXXX

Parameters added to the URL modify the filters on the fly.
https://salesforce.com/00Oa00000XXXXX?pn0=gt&pv0=999&pn1=eq&pv1=New%20York&pn2=eq&pv2=NY
Careful: parameter numbers start with zero

The updated filters are temporary.  But if the user decides to click the Save button after the results appear these parameters become permanent.  Yikes! You’d need to specify complete parameters every time to avoid unexpected results left over from a previous run.  Hmmm… I’m not loving that.  WAIT!  I have an idea.  Combine Power Query and parameters with a little VBA.

VBA PARAMETER SAVE method:  In Excel, I can select parameters for the report, a formula generates the URL with those parameters all combined and correct. It’s too easy to mess these up unless its automated.  A button click runs VBA that opens IE, navigates to the URL. When “not IE.Busy”, VBA executes the IE Save button, closes the browser, then updates Power Query.  Voila!  It actually does work.
The report is now saved with my current parameter set, but that’s a good thing. I’ll only use it with this Excel Power Query.  Each time, parameters are provided on the fly, the report is saved and Power Query will simply return the newly filtered results.  Still limited to 2000 rows. But at least I can filter the report easily to limit results as needed.

Meanwhile, we need a more robust solution.

What SALESFORCE.COM can do: Limiting report exports to 2000 rows is painful and outdated.  Given that Salesforce Reports do not allow SELECT DISTINCT (yep!) this is a huge point of frustration.  Sure I can remove duplicates in Excel easily, but I’m wasting some of my precious 2k allotment on dupes.  I can manually export the full report in the browser, but who wants to do anything manually any more.
Vote to increase the 2000 row limit here.
Vote to add DISTINCT rows here while you’re at it.

What MICROSOFT can do: Power Query doesn’t have any way to pass parameters to Salesforce Reports – yet. I’m hoping they will add that capability in the future.  I posted a request for that feature here.  Go vote for it now to increase the chances.