How to use query against Salesforce Object

[Anonymous]

Is there any way to query against the Salesforce data directly.

Gone thru the documentation- 

https://docs.microsoft.com/en-us/power-query/connectors/salesforceobjects

Where it is quoted - 

Known issues and limitations

• There is a limit on the number of fields a query to Salesforce can contain. The limit varies depending on the type of the columns, the number of computed columns, and so on. When you receive an error Query is either selecting too many fields or the filter conditions are too complicated it means that your query exceeds the limit. To avoid this, use the Select Query advanced option and specify fields that you really need.

I coudn't find the advanced option, please help.

[QueryWhiz]

Hi, as a workaround, maybe you can try to test your connection with a 3rd party connector. I've tried windsor.ai, supermetrics (which doesn't have PBI as a destination, so you first have to export to Google Sheets and then export to PBI) 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. 

[atoews]

Hi All - I have encountered this issue for several years due to our usage of several extensive managed packages that push the query over the PowerBI URI character length.  The MS Documentation Known Issues & Limitation does a good job describing the problem, but as noted not so great providing detail on how to use the Select Query Advanced Option.  Fortunately it's not hard at all using Advanced Editor if you can find the right syntax.  Below is an example of a boilerplate query of the Account object (per Power Query Editor Advanced Editor) which against my org returns the dreaded URI length error:

As you can see it is attempting to pull the entire Account object inclusive of all fields before getting to the ability to limit the selected fields.  To get around this just reference the Advanced Editor syntax for a smaller object where you CAN get to the Select Columns interface, then copy/paste that code into your Account query editor and tweek the details as needed to make it fit.  Example shown below, it's not exactly a SOQL query syntax but you get the pattern, just string together the API names of all the fields you want to pull down and you're good to go!

[DoryO]

No one has explained why Microsoft documentation for the Salesforce connector mentions using "Select Query advanced option" to limit columns. Is this a documentation error? Is this an OBDC feature not applicable to Salesforce connectors?

The Salesforce REST APIs allow for the parameters we need but Microsoft's connectors don't take advantage of that. So I see no way to limit rows or columns. It is too easy to exceed 2k row limit and even column limits in the initial "Get" before we can "transform". 

[amitchandak]

@Anonymous , Check are you getting advance option under connection

[Anonymous]

Hey @Amit, thanks for your reply. 

I am aware of this method, I would need to download the salesforce driver. 

I was looking for if I could query using the Salesforce Object connector.

[v-deddai1-msft · Accepted solution]

Hi @Anonymous ,

Using the built-in salesforce connector, it is only possible to query data after importing the whole table in Power BI Desktop. There is a similar idea about adding salesforce query in Power BI , please vote it up.

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Best Regards,

Dedmon Dai