Solved: Salesforce Lead Object Issue

hoshy · ‎05-12-2017

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.

hoshy · ‎05-22-2017

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.

View solution in original post

v-shex-msft · ‎05-14-2017

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

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.

hoshy · ‎05-15-2017

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 · ‎05-22-2017