How to filter data during import of Salesforce table?

[glennbuck523]

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,

[ReportMaster]

Hi @glennbuck523 were you able to find a solution? As a workaround, maybe you can try to test your connection with a 3rd party tool, which pulls data directly from the SF objects API so you won't face that records limit. I currently use windsor.ai and I can pre-filter my data on the tool's UI before exporting it to PBI, so when exporting it I just have the fields, date range and filter I selected and no more than that. 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. 

[AAInDallas]

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. 

[v-yuezhe-msft]

@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

[glennbuck523]

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

[v-yuezhe-msft]

@glennbuck523,

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

Regards,
Lydia

[glennbuck523]

@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...

[v-yuezhe-msft]

@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

[Aron_Moore]

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.)

[glennbuck523]

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,