cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
glennbuck523 Frequent Visitor
Frequent Visitor

How to filter data during import of Salesforce table?

Is there a way to filter data during the data load from salesforce tables?  This is compared to filtering the data AFTER all of the data has been loaded.

 

We are trying to connect to salesforce tables due to the limitation of loading from reports @ 2,000 records. However, certain tables have 100,000s records. We have filters we will apply to the data (recordtypeid, for instance).  We do not want to load the entire table and then filter the table during the power query steps since it takes a significant amount of time to load .  It would be much more efficient to restrict the data coming into PowerBI.  Can anyone tell me if this is possible with Salesforce and help with the syntax?

 

Conceptually speaking i think it would be possible in the Naviation step but have no idea what the syntax would be.  Filtering the Record type as a step after the data is loaded would be simply stated as below

 

= Source{[Name="Case__c"]}[Data]

 

= Table.SelectRows(Bid__c, each ([RecordTypeId] = "012U00000001d8PIAQ"))

 

Thanks,

8 REPLIES 8
Aron_Moore Established Member
Established Member

Re: How to filter data during import of Salesforce table?

Do you need the report(s) specifically?

 

The Objects connection does not have the row limitation (though you'll need to "rebuild" the "report" in Power BI i.e. bring in related objects, join, merge, etc.)

Capture.PNG

glennbuck523 Frequent Visitor
Frequent Visitor

Re: How to filter data during import of Salesforce table?

Hello,

  No, we can rebuild the report in PowerBI easy enough.  We just have several hundred thousand rows in the given table but only want about 5,000 of them at any given time so using the Report option doesn't work due to the limit.  But pulling back several hundred thousand records into PowerBI THEN filtering is a nightmare for performance.

 

Thanks,

Moderator v-yuezhe-msft
Moderator

Re: How to filter data during import of Salesforce table?

@glennbuck523,

I am afraid that it is not possible to add data filter before you import salesforce data to Power BI Desktop. 

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.

Regards,

Lydia

Community Support Team _ Lydia Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
glennbuck523 Frequent Visitor
Frequent Visitor

Re: How to filter data during import of Salesforce table?

Thanks, @v-yuezhe-msft for this.  We'll need to rope in our technical resources to go this route if this is the way we go.  Do you know if using the PowerBI Web version would be a potential workaround?  Perhaps creating a PowerBI Desktop connecting to tables then serving it up to the PowerBI Web which can auto-refresh? I tried a small sample and clicking refresh seems to go ultra fast but perhaps it was just refreshing the data already pulled back to just refresh the visual cahnges to the charts/graphs.  I tried connecting to Salesforce tables in PowerBI Web directly but it doesn't seem to have the ability (or my knowledge doesn't have the ability).

 

Thanks!

 Glenn

Moderator v-yuezhe-msft
Moderator

Re: How to filter data during import of Salesforce table?

@glennbuck523,

In Power BI Service, you are able to connect to salesforce content pack.

Regards,
Lydia

Community Support Team _ Lydia Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
glennbuck523 Frequent Visitor
Frequent Visitor

Re: How to filter data during import of Salesforce table?

@v-yuezhe-msft

 

I did start going down that route but then in all of the mentions it was noted there was a limit of 2k records so i stopped since i know my end result will be more than that.  I was playing around with connecting to the tables directly.  After i established filters in the powerquery itself it drastrically reduced the amount of records returned and time it took.  It seems like only the first time I connected, before i had a chance to add filters, was causing me to pull back the entire table.  I think my issue was connecting to the source the first time in the PowerBI visualization portion compared to in Query Editor was the culprit...

Moderator v-yuezhe-msft
Moderator

Re: How to filter data during import of Salesforce table?

@glennbuck523,

By using the built-in salesforce connector in Power BI Desktop, I am afraid that you are only able to add filter in Query Editor after importing the whole table.

Regards,
Lydia

Community Support Team _ Lydia Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
AAInDallas Frequent Visitor
Frequent Visitor

How to filter data during import of Salesforce table?

I have found that Power BI will record the steps of your query, including any filters you place on each column. 

 

So filter the column, Power BI records the step, apply and save. 

Note, this is when I am using the desktop designer. 

Helpful resources

Announcements
New Kudos Received Badges Coming

New Kudos Received Badges Coming

Kudos to you if you earned one of these! Check your inbox for a notification.

Microsoft Implementation for Communities Wins Award

Microsoft Implementation for Communities Wins Award

Learn about the award-winning innovation that was implemented across Microsoft’s Business Applications Communities.

Power Platform World Tour

Power Platform World Tour

Find out where you can attend!

Top Solution Authors
Top Kudoed Authors (Last 30 Days)
Users online (2,106)