Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
tamirk
Frequent Visitor

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



11 REPLIES 11
curth
Power BI Team
Power BI Team

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

tamirk
Frequent Visitor

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
Super User
Super User

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


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

Sorry about that...

I'm using Power BI Desktop

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.


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

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

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
Frequent Visitor

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"}

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
Frequent Visitor

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"}]

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.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors