Limit Table Expansion Power Query

[Anonymous]

Hello, 

My report is slowed down because I have to import a large dataset from Salesforce, and then filter on a small portion by latest date. My solution has been to find a way to filter on the date before importing, but I'd be open to a better way. Other threads mentioned SQL code but from my understanding that's not an option with Salesforce.

My problem is, the data is imported as a table and if I expand on the date column it will simply show the date for every single row in the table. 

Would it be possible to expand the date column in a summarized way? For example, one row for every unique date? Or would that take as much effort for PowerBI and still slow down the report?

Thank you in advance. 

[CoderZen08]

Hi,  were you able to find a solution? As a workaround, maybe you can try to test your connection with a 3rd party connector. 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. 

[v-piga-msft]

Hi @Anonymous ,

What about removing the duplicate date rows?

May be @ImkeF  have the better ways to help you.

Best Regards,

Cherry

[ImkeF]

I'm not familiar with Salesforce-Source, so cannot advise how to filter at source. Recommend to search in this forum for it.

But instead of expanding, you can try to add a column where you retriev the MAX of the "EffectiveDate"-column:

List.Max( [<YourColumnName>][Effective_Date__c] )

And then filter / remove dups  before expanding.

Impossible to predict performance in beforehand here.

[Anonymous]

Thanks for your response! That would require me to expand the column, which would still load every row.