Group By for Salesforce Query

[tamirk]

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 object I try, or group by option I try, the result is always empty, probably meaning an error some where (but there's no message to that).

Does anyone have any experience with such a case?

Thanks,

T

[curth]

Can you post the text of the query you're trying (or send it privately, if you prefer it to remain private)?

[tamirk]

Sure

let
Source = Salesforce.Data(),
Investment__c = Source{[Name="Investment__c"]}[Data],
#"Filtered Rows" = Table.SelectRows(Investment__c, each ([Status__c] <> "Cancelled")),
#"Removed Other Columns" = Table.SelectColumns(#"Filtered Rows",{"Id", "Name", "Amount__c", "Total_Amount_Received__c", "Commitment__c"}),
#"Grouped Rows" = Table.Group(#"Removed Other Columns", {"Commitment__c"}, {{"Count", each Table.RowCount(_), type number}})
in
#"Grouped Rows"

[Greg_Deckler]

First, are you using the service, Excel or Power BI Desktop when connecting to your Sales Force instance?

[tamirk]

Sorry about that...

I'm using Power BI Desktop

[Greg_Deckler]

As a work-a-round, you can use the matrix visualization to group the data after you have loaded it, or create a new query to create a table just on the column you want to group by, remove duplicates, and then link the talbles together, you can then use that to group things in your visualizations.

[tamirk]

Hi,

Thanks for suggesting that but it won't work for me. The example written here is a simplified version of the real case where I have sums and averages as well as counts of sevral fields.

My current workaround is to have the raw query in the model with a relation the the parent object (the group by field) and I do all the calculation in DAX. 

Thanks,

T

[curth]

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]

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]

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.

[tamirk]

Yes, this is from Fiddler (deleted the auth headers):

GET https://emea.salesforce.com/services/data/v29.0/query?q=SELECT%20Commitment__c%2CCOUNT(Id)%20FROM%20... HTTP/1.1
Accept: */*
User-Agent: Microsoft.Data.Mashup (https://go.microsoft.com/fwlink/?LinkID=304225)
Accept-Encoding: gzip, deflate
Host: emea.salesforce.com

HTTP/1.1 400 Bad Request
Date: Wed, 19 Aug 2015 07:03:53 GMT
Expires: Thu, 01 Jan 1970 00:00:00 GMT
Sforce-Limit-Info: api-usage=3983/23600
Content-Type: application/json;charset=UTF-8
Content-Length: 145

[{"message":"Aggregate query does not support queryMore(), use LIMIT to restrict the results to a single batch","errorCode":"EXCEEDED_ID_LIMIT"}]

[curth]

Wow, thanks for doing this. It looks like there's a basic limitation in SOQL which disallows aggregate queries that return more than 2000 rows. I've filed a bug to try to handle this better on our end.