SALES FORCE TO POWER BI HELP

[alisonpappas]

Hi All,

Hope you can help me out. I am attempting to connect  a sales force report to Power BI. The report max's out at 2k rows, the report has more, so i tried to connect through sales force "objects." When doing so, no reports show. Does anyone know a work around the 2k max rows so I can load the ENTIRE report from sales force directly into power bi?

thank you,

alison

[lbendlin]

Bluntly: No.  Salesforce will never lift the 2k row limit for the Reports API.

There are a large number of workarounds. 

Salesforce Objects connector _may_ work but only if you either have a report that only queries a single object or if you know the report type and the objects and lookups in that report type.  Power Query does some mild query folding on the SalesForce Object queries but it does not support custom SOQL and sub queries.

Another workaround is to use the CSV extracts from Salesforce and then ingest the CSV in Power Query.  This is my preferred method as it is really, really fast. Just make sure to set UTF-8 as the default encoding.  You can semi-automate the process by specifying the export intent in the URL like so (works for Classic and Lightning):

https://{customdomain}.my.salesforce.com/{reportid}?csv&isdtp=p1

Other options:  Use pagination, ask your IT department for object dumps, etc etc.

[alisonpappas]

@lbendlin 

Hi, thank you! can you please describe the CSV option, aka the steps of how to set this up please? i am pretty new so i do not know much about what this means but it seems like this will be my best option

[lbendlin]

Not much to it - set up the report in SFDC as desired. Get the report id.  Put it in the URL and click on the URL.  This will download the CSV file.

[alisonpappas]

Do I have to reconnect the report each time? Or do you suggest I have one master file connected in the query and I replace that with the contents of the new report each time downloaded?

[lbendlin]

I do the latter. I have a process monkey clicking the links and collecting the CSV files with fixed names into a sharepoint folder. Then I point Power BI to these CSV files.