group by breaks query folding

[akhaliq7]

How to use a alternative method to do a group by without breaking query folding?

[edhans]

No. It depends on what you are doing specifically. With SQL Server, it does work in many cases. Summing quantity by tracking number in the AdventureWorks DB for example returns this folded query:

select [rows].[CarrierTrackingNumber] as [CarrierTrackingNumber],
    sum([rows].[OrderQty]) as [Total Units]
from 
(
    select [CarrierTrackingNumber],
        [OrderQty]
    from [Sales].[SalesOrderDetail] as [$Table]
) as [rows]
group by [CarrierTrackingNumber]

Counting rows works too, as does average. Counting Distinct rows does not - folding breaks.  As does keeping the All Rows aggregation.

And your datasource might be different. Oracle or PostGRE SQL may or may not be supported for some Group By operations.

[akhaliq7]

Yes oracle db 

[edhans · Accepted solution]

If it breaks, you've no choice but to either let the mashup engine handle it locally, or talk to a DBA into creating a view for you. Power Query folding isn't universal across all sources that support some measure of folding. I'm surprised Oracle doesn't, but then again, not really surprised. That isn't the best connector in the world unfortunately.