{"id":65,"date":"2016-04-27T21:31:26","date_gmt":"2016-04-27T21:31:26","guid":{"rendered":"http:\/\/cat5sheetstorm.com\/?p=65"},"modified":"2016-04-27T22:39:34","modified_gmt":"2016-04-27T22:39:34","slug":"power-query-salesforce-reports-2000-row-limit","status":"publish","type":"post","link":"http:\/\/cat5sheetstorm.com\/?p=65","title":{"rendered":"Power Query & Salesforce Reports: 2000 row limit"},"content":{"rendered":"

\"SFXL\"<\/a>In 2014, Microsoft added a Salesforce Reports<\/strong> connector to Power Query <\/a>making it easy to pull data straight into Excel. \u00a0Awesome! Unfortunately, Salesforce limits results to\u00a02000 rows. \u00a0That’s not Microsoft’s fault. \u00a0That limit was set by Salesforce back in the Middle Ages when resources were scarce. \u00a0Or something. \u00a0I can’t explain it.\u00a0It sure does cause a lot of contortions to work around.<\/p>\n

Here are three\u00a0workarounds I’ve used\u00a0to exceed that 2k limit:<\/p>\n

VBA CONTROLLING IE<\/strong>:\u00a0 Manually exporting a report in the browser is not limited.\u00a0 So I wrote a VBA macro to fire up IE, log into Salesforce, run a report,\u00a0click the\u00a0Export button, download to Excel. Ugh. It works. It’s fragile. It’s a lot of code.<\/p>\n

APPEND method<\/strong>: This one’s MUCH easier. Create multiple copies of your report in Salesforce.com, each limited to a different “slice”\u00a0of the data. \u00a0Hopefully, each will be <2000 rows and when combined you’ll have a full dataset.\u00a0In Power Query, connect to each\u00a0report, setting all but one (the primary) to\u00a0“Load To…Only Create Connection<\/strong>“. \u00a0Then in the primary query\u00a0Combine…Append<\/strong> the others and load to the worksheet. \u00a0But if any of these queries exceeds 2000 rows, you’ll be missing data and there’s no error message to warn you.<\/p>\n

PARAMETER method<\/strong>: \u00a0You can run a\u00a0Salesforce Report in a browser with parameters in\u00a0the URL. By limiting the report to a small set of data you have a better chance of staying below 2k. \u00a0Javier Gonzalez <\/a>does a good job of explaining this in a blog post here<\/a>. \u00a0Basically, you\u00a0put\u00a0filters on your report in Salesforce report ahead of time. \u00a0The operator and the value are easy to modify\u00a0with parameters later. \u00a0But the field name is not. In fact, placeholders that allow all data to pass the saved filters might be a good idea.<\/p>\n

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.
\nExample URL: https:\/\/salesforce.com\/00Oa00000XXXXX<\/strong><\/p>\n

\"ReportBefore\"<\/a><\/p>\n

Parameters added to the\u00a0URL modify\u00a0the filters on the fly.
\nhttps:\/\/salesforce.com\/00Oa00000XXXXX?pn0=gt&pv0=999&pn1=eq&pv1=New%20York&pn2=eq&pv2=NY
\n\"ReportAfter\"<\/a><\/strong>Careful: parameter numbers start with\u00a0zero<\/p>\n

The updated filters are temporary. \u00a0But if the user decides to\u00a0click the Save button<\/strong>\u00a0after the results appear\u00a0these\u00a0parameters become permanent. \u00a0Yikes! You’d need\u00a0to specify complete\u00a0parameters every time to avoid unexpected\u00a0results left over from a previous run. \u00a0Hmmm… I’m not loving that. \u00a0WAIT! \u00a0I have an idea. \u00a0Combine Power Query and parameters with a little VBA.<\/p>\n

VBA PARAMETER SAVE<\/strong> method: \u00a0In Excel, I can select parameters for the report, a formula\u00a0generates the URL with those parameters all combined and correct. It’s too easy to mess these up unless its automated. \u00a0A button click runs VBA that opens IE, navigates to the\u00a0URL. When “not\u00a0IE.Busy”, VBA executes the IE Save button, closes the browser, then updates Power Query. \u00a0Voila! \u00a0It actually does work.
\nThe report is now saved with my current\u00a0parameter set, but that’s a good thing. I’ll only use it with this Excel Power Query. \u00a0Each time, parameters\u00a0are provided on the fly, the report is saved and Power Query will simply return the newly filtered results. \u00a0Still limited to\u00a02000 rows. But at least I can filter\u00a0the\u00a0report easily to limit results as needed.<\/p>\n

Meanwhile, we need a more robust solution.<\/p>\n

What SALESFORCE.COM can do<\/strong>: Limiting report exports\u00a0to 2000 rows is painful\u00a0and outdated. \u00a0Given that Salesforce Reports do not allow SELECT DISTINCT (yep!) this is a huge point of frustration. \u00a0Sure I can remove duplicates in Excel easily, but I’m wasting some of my\u00a0precious 2k allotment on dupes. \u00a0I\u00a0can manually<\/span> export the full report in the browser, but who wants to do anything manually any more.
\nVote to increase the 2000 row\u00a0limit here<\/a>.
\nVote to add DISTINCT rows\u00a0here<\/a>\u00a0while you’re at it.<\/p>\n

What MICROSOFT can do:<\/strong> Power Query doesn’t have any way to pass parameters to Salesforce Reports – yet. I’m hoping they\u00a0will add that capability in the\u00a0future. \u00a0I posted a request for that feature here<\/a>. \u00a0Go vote for it now to increase the\u00a0chances.<\/p>\n

 <\/p>\n\"twitter\"<\/a>\"mail\"<\/a>","protected":false},"excerpt":{"rendered":"

In 2014, Microsoft added a Salesforce Reports connector to Power Query making it easy to pull data straight into Excel. \u00a0Awesome! Unfortunately, Salesforce limits results to\u00a02000 rows. \u00a0That’s not Microsoft’s fault. \u00a0That limit was set by Salesforce back in the Middle Ages when resources were scarce. \u00a0Or something. \u00a0I can’t explain it.\u00a0It sure does cause … Continue reading “Power Query & Salesforce Reports: 2000 row limit”<\/span><\/a><\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"_jetpack_memberships_contains_paid_content":false,"footnotes":""},"categories":[1],"tags":[],"class_list":["post-65","post","type-post","status-publish","format-standard","hentry","category-uncategorized"],"jetpack_featured_media_url":"","jetpack_sharing_enabled":true,"_links":{"self":[{"href":"http:\/\/cat5sheetstorm.com\/index.php?rest_route=\/wp\/v2\/posts\/65","targetHints":{"allow":["GET"]}}],"collection":[{"href":"http:\/\/cat5sheetstorm.com\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"http:\/\/cat5sheetstorm.com\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"http:\/\/cat5sheetstorm.com\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"http:\/\/cat5sheetstorm.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=65"}],"version-history":[{"count":7,"href":"http:\/\/cat5sheetstorm.com\/index.php?rest_route=\/wp\/v2\/posts\/65\/revisions"}],"predecessor-version":[{"id":77,"href":"http:\/\/cat5sheetstorm.com\/index.php?rest_route=\/wp\/v2\/posts\/65\/revisions\/77"}],"wp:attachment":[{"href":"http:\/\/cat5sheetstorm.com\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=65"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"http:\/\/cat5sheetstorm.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=65"},{"taxonomy":"post_tag","embeddable":true,"href":"http:\/\/cat5sheetstorm.com\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=65"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}