Salesforce Lead Object Issue

[hoshy]

Hi there,

Trying to filter salesforce leads first down by last 12 months (works), then extract month and year so I can do a group to get average leads on a monthly basis but I keep hitting the problem of:

DataSource.Error: Non-grouped query that uses overall aggregate functions cannot also use LIMIT
Details:
    List

I'm lead to believe there's a 2000 record limit but as I do my filtering first I didn't expect that is a problem - I do similar things with extracting columns on Opportunities records where I don't have over 2000 total records so it works but now I'm afraid my dashboards will die as the business grows.

Any suggestions please?

Here's my broken query:

let
    Source = Lead,
    #"Removed Columns" = //big list of columns,
    #"Filtered Rows" = Table.SelectRows(#"Removed Columns", each ([LeadSource] = "Quote" or [LeadSource] = "Trial" or [LeadSource] = "Download")),
    #"Filtered Rows1" = Table.SelectRows(#"Filtered Rows", each Date.IsInPreviousNMonths([CreatedDate], 12)),
    #"Inserted Month" = Table.AddColumn(#"Filtered Rows1", "Month", each Date.Month([CreatedDate]), type number)
in
    #"Inserted Month"

Thanks for any help.

[ReportMaster]

Hi @hoshy were you able to find a solution? As a workaround, maybe you can try to test your connection with a 3rd party connector, which pulls data directly from the SF objects API and does not even have the 2k rows limitation, also this one shouldn't give you this problem you were facing. 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-shex-msft]

Hi @hoshy,

Based on my research, you should specify a column as the group to enable the limit function. limit function is similar as top n function, if your query contains multiple columns, you should add a group column to enable it.

You can also take a look at below link which use limit function:

Execution Governors and Limits

BTW: Use single column with limit function not need to group.

Regards,

Xiaoxin Sheng

[hoshy]

Hi @v-shex-msft,

Thanks very much for the reply.

Right now I ended up using a report that pre-filtered the data so this works around my issue - I'm not happy with this solution but at least it works. 

To be honest, I don't really understand your answer.  "specify a column as the group to enable the limit function" - the error says that I *can't* use the limit function so I'm not sure this helps. 

"if your query contains multiple columns, you should add a group column to enable it" - I do have multiple columns but I want to add the group by month, I won't be able to do that if I group the data by something else first.

It's entirely possible I have misunderstood your advice as I'm quite new to this. 

Ashley

[hoshy · Accepted solution]

Ok, so it turns out there is an actual bug here.  @v-shex-msft hopefully you can create a ticket from this (I'm assuming you're MS given your username?).

For the benefit of the wider interwebs here's the bug, and work-around.

Recreation:

Datasource: SFDC Objects

Object:  Anything with a datetime, date only can also be problematic in similar ways.

Transform:  Date->Month (month number, fails for both add column or transform.  others like year number fail too). 

Boom: DataSource.Error: Non-grouped query that uses overall aggregate functions cannot also use LIMIT

This used to only be a problem on larger tables like Leads - hence the above post.  Since at least 22/5/2017 this seems to affect even tiny tables with only a few rows.  TRied with April both and May PBI Desktop releases.  Something seems to have changed on SFDC side as both PBI releases gave same result but for smaller tables this DEFINITELY worked in April release, and pretty sure May too - as said, larger tables were always an issue but the below workaround works on even my largest tables.

Example broken query:

let
    Source = Salesforce.Data(),
    ForecastingQuota = Source{[Name="ForecastingQuota"]}[Data],
    #"Extracted Month" = Table.TransformColumns(ForecastingQuota,{{"StartDate", Date.Month}})
in
    #"Extracted Month"

Workaround:

If you have a date only field, just add a new column that extracts the "date only", basically you end up with a duplicate column but you can then successfully extract the month successfully.

Working query:

let
    Source = Salesforce.Data(),
    ForecastingQuota = Source{[Name="ForecastingQuota"]}[Data],
    #"Inserted Date" = Table.AddColumn(ForecastingQuota, "Date", each DateTime.Date([StartDate]), type date),
    #"Inserted Month" = Table.AddColumn(#"Inserted Date", "Month", each Date.Month([Date]), type number)
in
    #"Inserted Month"

Hope that helps save somebody else 5 hours of debugging a heavily nested set of reports...

Quick update: if you have multiple columns you don't seem to need to make duplicates of them all - as soon as one column is "fixed" the Transform/Add column works for others.

[v-shex-msft]

Hi @hoshy,

I can reproduce your issue. I will report this to power bi team and update here once it has been fixed.

Regards,

Xiaoxin Sheng

[hoshy]

Thanks @v-shex-msft