Salesforce and large query

[jdballard30]

I have Salesforce.com as a data source. It's querying a large table that in the last few days went from several thousand rows to almost 500,000 (which was expected.) Now, when I refresh Power BI it's taking forever and occasionally timing out on the query of that object. (The object is Task if you're curious.) I'm querying the table and immediately filtering to the last five days, but that's not helping because I'm assuming Power BI is returning everything and taking forever to do it, and then filtering to the last five days. Assuming that's the case, is there any way to force Power BI to make the API request include the filter, so Salesforce does the filtering. I really only need the last few days of data returned to me, and without that filter, it's taking forever.

[v-yuezhe-msft · Accepted solution]

@jdballard30,

Using the built-in salesforce connector, it is only possible to filter data after importing the whole table in Power BI Desktop. There is a similar idea about adding salesforce query in Power BI , please vote it up.

In your scenario,  you can check the following options to work around this issue.

1. You can contact Salesforce support to check if it is possible to add filter in Salesforce Rest API. You can use Web connector in Power BI Desktop to connect to the API with filtered data.

2. Create a specific report in Salesforce and connect to the Salesforce report in Power BI.

Regards,
Lydia

[jdballard30]

Thanks, Lydia. I had gone ahead with the report idea. I'd considered using the web connector, but I thought I recalled reading somewhere that web connectors couldn't use a scheduled refresh. I'm not sure if that was for a prior version, and it's now possible, but being able to set up a scheduled refresh is required in this case.

[v-yuezhe-msft]

@jdballard30,

It is possible to set schedule refresh in this case.

Regards,
Lydia