Forum Discussion

tamirk's avatar
tamirk
Frequent Visitor
11 years ago

Group By for Salesforce Query

Hi,   I'm using a salesforce datasource and I wanted to do a group by over an object. This seems like a simple thing and it works fine in other sources, but in salesforce it doesn't. No matter what...
Need Help
curth's avatar
curth
Power BI Team
11 years ago

So of course I can't see your custom table definition, but this very similar query works for me:

 

let
    Source = Salesforce.Data(),
    Account1 = Source{[Name="Account"]}[Data],
    #"Removed Other Columns" = Table.SelectColumns(Account1,{"Name", "Type", "BillingCity", "BillingState"}),
    #"Grouped Rows" = Table.Group(#"Removed Other Columns", {"Type"}, {{"Count", each Table.RowCount(_), type number}})
in
    #"Grouped Rows"

 

 

Could you turn on tracing and then repeat the steps you performed in the editor? Then look at your trace logs for an entry which looks like this:

 

14T16:43:40.1023644Z","Action":"Engine/IO/Web/Request/GetResponse","RequestMethod":"GET","RequestUri":"https://na10.salesforce.com/services/data/v29.0/query?q=SELECT Type,COUNT(Id) FROM Account GROUP BY Type LIMIT 1000","RequestHasContent":"False","RequestHasHeaders":"False","RequestHasTimeout":"True","UseCache":"True","ResponseStatusCode":"200","ProductVersion":"2.26.0.0

 

The goal is to see what SOQL we're generating for your query and whether or not it might have a problem.

tamirk's avatar
tamirk
Frequent Visitor
11 years ago

Follows. 

The SQL looks similar to yours

 

DataMashup.Trace Error: 24579 : {"Start":"2015-08-14T17:16:15.4316580Z","Action":"Engine/IO/Web/Request/GetResponse","RequestMethod":"GET","RequestUri":"https://emea.salesforce.com/services/data/v29.0/query?q=SELECT Commitment__c,COUNT(Id) FROM Investment__c WHERE (Status__c<>'Cancelled') GROUP BY Commitment__c","RequestHasContent":"False","RequestHasHeaders":"False","RequestHasTimeout":"True","UseCache":"True","Exception":"Exception:\r\nExceptionType: System.Net.WebException, System, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b12a3c456789e000\r\nMessage: The remote server returned an error: (400) Bad Request.\r\nStackTrace:\n   at System.Net.HttpWebRequest.GetResponse()\r\n   at Microsoft.Mashup.Engine1.Library.Web.Request.CreateResponse(ResourceCredentialCollection credentials)\r\n   at Microsoft.Mashup.Engine1.Library.Web.Request.GetResponseCore(ResourceCredentialCollection credentials)\r\n   at Microsoft.Mashup.Engine1.Library.Common.RetryPolicy.Execute[TResult](IEngineHost host, Func`1 func)\r\n   at Microsoft.Mashup.Engine1.Library.Web.Request.GetResponse(ResourceCredentialCollection credentials, RetryPolicy retryPolicy, AuthorizationThrowFunction authorizationFunction)\r\n\r\n\r\n","ProductVersion":"2.25.4095.554 (Release-V2-Private)","ActivityId":"53705023-fdbb-4f70-87cd-10edaf0433a1","Process":"Microsoft.Mashup.Container.NetFX40","Pid":8608,"Tid":5,"Duration":"00:00:00.9311327"}

  • curth's avatar
    curth
    Power BI Team
    11 years ago

    Are you familiar with Fiddler or other network tools? I'd like to see the error message being returned by Salesforce in this case, but there's no way to do it without looking at your network traffic.