Forum Discussion
Count, Where and Join
I am bringing in Objects from Salesforce, for example one object is contacts. I have over 8 mllion contacts in Salesforce and would like to only bring in the contacts that have a ICL Code. I tried using a report but6 it limits me to the 2000. I do not want to Filter after the data has been loaded since the refresh time takes to long. I am not familar with Power query and unsure how to create the where statement prior to loading the data into the excel sheet. I was hoping someone could help me out
7 Replies
Hi Dave
No when I apply the filter and then do the dataload it still cycles thru the 8 million records before it displays in the filtered columns query so the refresh takes my users 30 minutes before they can access the report. I want the filter to occur before so when it refreshes it will take only a few minutes.
Thanks
Debbie
Hi Debbie,
From what you're describing it sounds like you need to apply a SQL query prior to loading it into the Power Query, but this only works if the data is in a server that you can access. I'm assuming you are using the Salesforce data connector and going by this link, it appears that there are api restrictions which is why you are experiencing the slow load time on the data.
Sorry I can be of any help.
Hi doliver29 were you able to find a solution? As a workaround, maybe you can try to test your connection with a 3rd party connector, which pulls the data directly from SF objects API and also lets you pre-filter the data on the tool UI before moving it to Power BI, so seems it's just what you were looking for. I've tried windsor.ai, supermetrics and funnel.io. I stayed with windsor because it is much cheaper so just to let you know other options. In case you wonder, to make the connection first search for the Salesforce connector in the data sources list:
After that, just grant access to your Salesforce account using your credentials, then on preview and destination page you will see a preview of your Salesforce fields:
There just select the fields you need. It is also compatible with custom fields and custom objects, so you'll be able to export them through windsor. Finally, just select PBI as your data destination and finally just copy and paste the url on PBI --> Get Data --> Web --> Paste the url.
Hi doliver29,
With eight million Contacts, the ICL Code filter should be applied in Salesforce before export.
Power BI Connector for Salesforce supports source-side filters and SOQL, so only matching Contacts are transferred to Power BI.
AppExchange and 30-day trial:
https://appexchange.salesforce.com/appxListingDetail?listingId=31526f0e-abd8-4cb5-bd1a-3bd56b5c0577Docs and support:
https://metricasoftware.com/docs/salesforce/
https://metricasoftware.com/docs/salesforce/contact-support/Cheers,
Metrica Team
