Filtering Salesforce data before loading it to the model

[erantdo]

Hi,

Lately I've been dealing with Salesforce object that amount to 10,000 items, but i want to work with only some of them that match a certain criteria (usually a date).
I hate loading 50,000+ items only to then filter out most of them.
Now, I can't use a report for that because then I'm limited to 2,000 items in my import.

Is there a way to do the filter when importing the data from Ssalesforce? I've searched everywhere for an answer.

Thanks a lot,
Eran

 

[Brian Smith]

I haven't specifically done this with Salesforce but I had a similar issue when loading data from an SSAS tabular model into excel workbooks. I found that it was very slow to refresh and difficult to manipulate in the preview because (at least I suspected) of the large data volume (although that explanation never really seamed satisfactory). In that case we were able to connect powerpivot to the same data source and use the query function there to bring in a much smaller subset of the data and load it directly to our data model which was where we wanted it anyway (I believe it is referred to as an MDX query but I don't recall for sure). If you can link in to sales force with power pivot that might be a workable solution.

 

[erantdo]

Thanks a lot, will try that!

btw, do you have an idea how to implement that in Power BI Desktop..?

 

[Ken Puls]

So two things here...

Power Query doesn't actually load all the rows of data. It loads a preview, where you then set up your filters. The actual query is then mashed up and applied as the data comes in. So while it looks like you're loading all rows, you're actually loading a filtered data set. The issue really is that Power Query feels slow.

Re Power BI Desktop, the "Get Data" layer is Power Query. So that's pretty much your only option there.

 

[konijay]

You should create a "report" in salesforce first then import that into PQ. there is a row limit on report preview which is 2000. PQ only imports whatever the default row limit is even though the report will output more than that. Talk to salesforce to increase that limit.