Need Help - Salesforce Object DataSource.Error

[akhatri]

Hi all,

I have a number of dashboard which I have built using the Salesforce Object connector and they have been working great, last week when I went back to them to adjust a couple things I now keep getting the below error at the Navigation stage in the steps applied.

Please could someone shed some light on what is happening here and how I can fix this.

Thanks in advance

Atish

DataSource.Error: Web.Contents failed to get contents from 'https://hdscorp.my.salesforce.com/services/data/v29.0/query?q=SELECT%20Id%2CIsDeleted%2CMasterRecord...' (414): URI Too Long
Details:
DataSourceKind=Salesforce
DataSourcePath=https://hdscorp.my.salesforce.com/
Url=https://hdscorp.my.salesforce.com/services/data/v29.0/query?q=SELECT%20Id%2CIsDeleted%2CMasterRecord......

[metrica]

Hi @akhatri 

For anyone hitting this thread with the same 414 URI Too Long error - worth knowing that Power Query's column-pruning steps (Table.SelectColumns, Table.RemoveColumns) only help if they fold back into the SOQL sent to Salesforce. Whether they do depends on where they sit in the chain and on the connector version, so the accepted solution can work in some cases but not always.

The most reliable native fix is the Salesforce Objects connector's Select Query (advanced) option when first connecting - there you supply a custom SOQL like SELECT Id, Name, AccountNumber FROM Account listing only the fields you need. Since SOQL is supplied directly, the query stays short and 414 doesn't come up, regardless of folding behavior.

As a workaround if hand-writing SOQL isn't ideal, Metrica Power BI Connector for Salesforce on AppExchange lets you pick fields visually through a UI inside Salesforce - same effect (short query, no 414), without needing to write the SOQL yourself.

Free 30-day trial if you'd like to test it. Setup guides at https://metricasoftware.com/, demo available on request.

Cheers,

Metrica Team.

[ReportGuru]

Hi @akhatri I know this reply is very late. I've used the Salesforce connector but I had some issues. As a workarodund, 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-yingjl]

Hi @akhatri ,

As far as I know, too many columns in your source may could casue this issue, please try to remove some unnecessary columns or filter some columns when loading into power query.

Please refer:

1. How to increase number of columns imported from Salesforce object or select the columns to load 
2. increase number of columns that can be imported using OData Feed 

Best Regards,
Community Support Team _ Yingjie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[akhatri]

Hi V-yingjl

I am able to see the columns I have filtered to after I apply a "Removed Other Columns" function, however can only see the columns I have filtered to. I need to see which other columns are also available in the object.

My query is as follows:

let
Source = Salesforce.Data(),
Account1 = Source{[Name="Account"]}[Data],
#"Removed Other Columns" = Table.SelectColumns(Account1,{"Id", "IsDeleted", "Name", "OwnerId", "Account_Segmentation_Type__c", "Geo2__c", "Region2__c", "District2__c"}),
#"Filtered Rows" = Table.SelectRows(#"Removed Other Columns", each [IsDeleted] = false),
#"Filtered Rows1" = Table.SelectRows(#"Filtered Rows", each not Text.Contains([Name], "MCF") or not Text.Contains([Name], "Forecast")),
#"Filtered Rows2" = Table.SelectRows(#"Filtered Rows1", each not Text.Contains([Name], "Delete") or not Text.Contains([Name], "Do Not Use")),
#"Added Custom" = Table.AddColumn(#"Filtered Rows2", "Account Segmentation Index", each if [Account_Segmentation_Type__c] = "Global" then 1 else
if [Account_Segmentation_Type__c] = "Regional" then 2 else
if [Account_Segmentation_Type__c] = "Enterprise" then 3 else
if [Account_Segmentation_Type__c] = "Commercial" then 4 else
if [Account_Segmentation_Type__c] = "Partners" then 5 else 6)
in
#"Added Custom"

[v-yingjl · Accepted solution]

Hi @akhatri ,

You can use Table.Removecolumns() to remove the filtered columns from the source table to get the retain columns:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("VdFdDsQgCATgu/S5SWXwr2dpev9rbEGdxRcStvLJuM9ziFxyISEd5yG4pHyN3NZ0K/oVZCv2m/qpxK/peE8nEIlKwkuxIlaqOdbC2zzKJDQSbRFovJHiOOVL2RbQReRIdBKyNh93ww40boEtSInEzSDCwcyZutlfO4kaCKRF6D9DX7n8TT2Di+BbtEgIgxRem+kQk+lMokcC2z/iQaBcoFGUbYs7EsotEtMICaXTxutOQlI0MtcAJ2WNj2AtlPf9AQ==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t, Column2 = _t, Column3 = _t, Column4 = _t, Column5 = _t, Column6 = _t, Column7 = _t, Column8 = _t, Column9 = _t, Column10 = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type date}, {"Column2", type date}, {"Column3", Int64.Type}, {"Column4", Int64.Type}, {"Column5", Int64.Type}, {"Column6", Int64.Type}, {"Column7", Int64.Type}, {"Column8", Int64.Type}, {"Column9", Int64.Type}, {"Column10", Int64.Type}}),
    #"Removed Other Columns" = Table.SelectColumns(#"Changed Type",{"Column1", "Column3", "Column4", "Column5", "Column6"}),
    #"Remain columns" = Table.RemoveColumns(#"Changed Type",{"Column1", "Column3", "Column4", "Column5", "Column6"})
in
    #"Remain columns"

#"Removed Other Columns" will return the filtered table and #"Remain columns" will return the retained table.

Best Regards,
Community Support Team _ Yingjie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.