Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
How to use a alternative method to do a group by without breaking query folding?
Solved! Go to 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.
DAX is for Analysis. Power Query is for Data Modeling
Proud to be a Super User!
MCSA: BI ReportingNo. 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.
DAX is for Analysis. Power Query is for Data Modeling
Proud to be a Super User!
MCSA: BI ReportingYes oracle db
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.
DAX is for Analysis. Power Query is for Data Modeling
Proud to be a Super User!
MCSA: BI ReportingCovering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.