LOAD ..., Sum(x) RESIDENT t GROUP BY ...

Group By in the load script in Power BI

GROUP BY in a Qlik load pre-aggregates a table. Power Query's Group by (Table.Group) is the direct equivalent. In Power BI you often don't need it at all, because measures aggregate at query time.

Direct equivalent Power Query DAX

Short answer: Use Transform > Group by, or Table.Group(T, {"Customer"}, {{"Total", each List.Sum([Amount]), type number}}). Before porting, ask whether a measure would do.

What it does in Qlik

A LOAD with GROUP BY aggregates rows. Every non-aggregated field must be in the group-by list. It is typically a resident load that creates a summary or snapshot table.

Qlik example

Qlik script

CustomerSummary:
LOAD CustomerID, Sum(Amount) AS TotalAmount, Count(OrderID) AS Orders, Max(OrderDate) AS LastOrder
RESIDENT Orders
GROUP BY CustomerID;

Power BI equivalent

Power Query

Power Query M

= Table.Group(Orders, {"CustomerID"}, {
    {"TotalAmount", each List.Sum([Amount]), type nullable number},
    {"Orders", each Table.RowCount(_), Int64.Type},
    {"LastOrder", each List.Max([OrderDate]), type nullable date}
  })

Or just measures

DAX

Total amount = SUM ( Orders[Amount] )
Orders = COUNTROWS ( Orders )
Last order = MAX ( Orders[OrderDate] )

Gotchas and behaviour differences

Count(OrderID) in Qlik counts non-null values. Table.RowCount(_) counts rows. Use List.NonNullCount([OrderID]) for the exact equivalent.
Group by folds to SQL on most relational sources. On files, it runs in the mashup engine.
A pre-aggregated table loses detail. Keep the detail table too if users need to drill down.

Official documentation

Load (GROUP BY clause) (Qlik Help)
Group by (Power Query) (Microsoft Learn)
Table.Group (Power Query M) (Microsoft Learn)

LOAD ... RESIDENT table; DROP TABLE table; Resident load and intermediate tables A Resident load reads a table already in memory. The Power Query equivalent is a referenced query, but it re-runs its source instead of reading from memory. Heavy multi-step transformations are better done upstream in a warehouse, dataflow or lakehouse. Partial / different approach Avg(Aggr(Sum(Sales), Customer)) Aggr (nested aggregation) Aggr builds a virtual table of aggregated values, for example sales per customer, so you can aggregate again. In DAX that is an iterator (AVERAGEX, MAXX, COUNTROWS(FILTER(...))) over VALUES or SUMMARIZE that calls a measure. Partial / different approach Count(DISTINCT CustomerID) Count, Count(DISTINCT) and counting key fields Count(DISTINCT Field) maps to DISTINCTCOUNT. Watch out for blanks, for which table you count from, and for the Qlik habit of counting key fields. Direct equivalent

Spotted an error or a better pattern? QlikToDax is maintained by a working BI consultant; corrections are welcome via the About page. Last reviewed 2026-10-25.