cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
tamirk Frequent Visitor
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
Super User
Super User

Re: Group By for Salesforce Query

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


I have book! Learn Power BI from Packt


Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!

tamirk Frequent Visitor
Frequent Visitor

Re: Group By for Salesforce Query

Sorry about that...

I'm using Power BI Desktop

Power BI Team curth
Power BI Team

Re: Group By for Salesforce Query

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

Re: Group By for Salesforce Query

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"

Super User
Super User

Re: Group By for Salesforce Query

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.


I have book! Learn Power BI from Packt


Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!

tamirk Frequent Visitor
Frequent Visitor

Re: Group By for Salesforce Query

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

Power BI Team curth
Power BI Team

Re: Group By for Salesforce Query

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

Re: Group By for Salesforce Query

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

Power BI Team curth
Power BI Team

Re: Group By for Salesforce Query

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.

Helpful resources

Announcements
New Topics Started Badges Coming

New Topics Started Badges Coming

We're releasing new versions of the badge that everyone's talking about. ;) Check your inbox for notifications.

MBAS 2020

Save the new date (and location)!

Our business applications community is growing—so we needed a different venue, resulting in a new date and location. See you there!

Difinity Conference

Difinity Conference

The largest Power BI, Power Platform, and Data conference in New Zealand

Top Solution Authors
Top Kudoed Authors (Last 30 Days)