Forum Discussion
Power Query suddenly extremely slow when materialising tables (Group / RowCount / Buffer)
Hi all,
I'm trying to determine whether anyone else is seeing similar behaviour in either Power BI Service or Power BI Desktop.
Background
I have a number of Power BI Gen1 dataflows using a fairly standard layered architecture (BRONZE → SILVER style processing). The issue appeared suddenly around mid-July with no intentional changes to the query logic.
The affected queries are not especially large by Power BI standards.
The issue
Queries run reasonably well until they hit a step which forces full evaluation/materialisation of the table.
Examples include:
- Table.Group
- Table.Buffer
- Table.RowCount
These operations are taking dramatically longer than expected.
Examples
- Dataset around 46,000 rows: Table.Buffer taking around a minute
- Dataset around 165,000 rows: Table.Group taking around 4.5 minutes
- Table.RowCount on an intermediate table - timing out at 10 minutes
- Larger datasets (c. 500,000 rows) - Timing out in the Service after 10 minutes
Specific example - see screenshots:
- The dates are filtered from 01/04/2022 so after Step3 - FilterDates there are 1,547 rows.
- The XFORM_AllocationIRO table brings in 1,187 rows
- This means that at the ExpandIRODates step (step 5) there are 1,836,289 rows. Step 5 takes just 2.06 seconds to run - even with almost 2 million rows.
- FilterIRODates (step 7) reduces the row count to 453,142. This step takes 1.76 seconds to run
So the GroupIRODates step is only trying to group those 453,142 rows - I would expect it to take a little longer than the previous steps because of the nature of grouping - but this should absolutely be no more than 20 seconds at the very most (even that would be a little slower than expected)
What I've tested
- Data volume - reduced datasets significantly, the behaviour persists even on much smaller data volumes.
- Different datasets - issue is consistent across multiple datasets.
- Service vs Desktop - tested both Power BI Service (Gen1 Dataflows) and Power BI Desktop. Desktop is slightly faster but still far slower than expected.
- Infrastructure - ICT have investigated the environment and appear to have addressed at least part of the issue through increasing the capacity of the server, but can see no other issues. At one stage I thought the issue was resolved because grouping became much faster. However I later realised I had accidentally left a filter on restricting the data to approximately 3 months of history. Removing that filter and re-testing showed the problem still exists.
- Data retrieval - the dataflow source itself is tiny (only a few MB being transferred), so this doesn't appear to be a network throughput issue.
The thing that doesn't make sense to me is the performance relative to row count. I wouldn't normally expect:
- Row counts taking several minutes
- Group operations taking several minutes on ~165k rows
- Materialisation operations becoming the dominant cost
My expectation and experience prior to last week would be that these operations should complete in seconds rather than minutes.
The slowdown appears specifically when the engine is forced to fully evaluate the table. Streaming-style operations appear much less affected with standard steps like filtering, typing, choosing columns etc all working fine. Merge and Expand steps are working as long as they are not preceeded by a group type step - if they are proceeded by a group type step they are then also taking several minutes if they are running at all.
In Desktop CPU usage increases significantly, memory usage increases significantly, laptop fans go into overdrive during execution which suggests the time is being spent processing rather than waiting on the source.
Question
Has anyone else seen recently:
- Significant degradation in Table.Group
- Significant degradation in Table.RowCount
- Significant degradation in Table.Buffer
- Problems specifically linked to table materialisation
Similar behaviour affecting both Desktop and Service
Particularly interested if anyone is running Gen1 Dataflows and noticed changes in the last couple of weeks.Any ideas or similar experiences would be much appreciated.
Thanks!
5 Replies
hey DataGirl, interesting findings you have there.
A few observations:
- The fact that filtering, column selection, and expansion steps remain fast while materialization steps become the bottleneck could mean that the slowdown is occurring inside the Power Query evaluation engine rather than at the source.
- Table.RowCount timing out on intermediate tables is particularly telling, since it forces full evaluation but performs minimal transformation logic
Because you're seeing the same behavior in both Desktop and Gen1 Dataflows, and across multiple datasets, it seems less likely to be query-specific and more likely related to a recent engine or infrastructure change.
As a troubleshooting step, I'd recommend capturing Power Query Diagnostics in Desktop and comparing timings before and after the materialization step.https://learn.microsoft.com/en-us/power-query/query-diagnostics
That may help identify whether the engine is spending time evaluating, buffering, or spilling data to disk.
Appreciate if you can "Kudos" and/or "Accept as Solution" if this answered your query.I couldn't see anything specific in the diagnostics in desktop - although I did also keep getting this error: Unexpected error: Container exited unexpectedly with code 0x80131623. PID: 13792.
Used features: (none).
I have done some more testing in the service to try and narrow down the issue - summary below (thanks to Co-pilot):Summary of findings
Initially there was a concern that the issue may have been caused by an accidental Cartesian expansion. I have now ruled this out.
The query structure is:
- Date table filtered to reporting period = 1,547 rows
- IRO allocation table = 1,187 rows
- Expanded result = 1,836,289 rows
- Active-date filter reduces this to 453,142 rows
Performance by step
Step Row Count Duration
ExpandIRODates 1,836,289 2.06 seconds FilterIRODates 453,142 1.76 seconds Distinct Keys Test 453,142 A few seconds Table.Group (RowCount only) 453,142 3m 15s Table.Group (alternative test) 453,142 3m 41s Additional testing
To identify where the slowdown occurs, I replaced the original grouping logic with a number of simpler tests.
Test 1 – Distinct Keys
Using only:
Table.Distinct( Table.SelectColumns( FilterIRODates, {"Date","CLAID","IROStartDate"} ) )This completed in a few seconds.
This demonstrates that:
- The row volume is not the issue.
- Generating the grouping keys is not the issue.
- The date expansion is not the issue.
Test 2 – Simplified Group
Using:
Table.Group( FilterIRODates, {"Date","CLAID","IROStartDate"}, {{"Rows", each Table.RowCount(_), Int64.Type}} )This took over 3 minutes.
This removes any complex aggregation logic and simply counts rows within each group.
Conclusion
The evidence now suggests that the bottleneck is specifically within the Table.Group aggregation/materialisation phase.
The following have effectively been ruled out:
- Dataflow retrieval
- Dataset size
- Cartesian expansion
- Date filtering logic
- Distinct key generation
- The specific List.Max(IROEndDate) aggregation used in the original query
The issue appears to occur as soon as Power Query has to create and process grouped partitions, even when the aggregation itself is extremely simple.
At this stage the behaviour seems disproportionate to the workload involved, given that:
- 1.8 million rows can be expanded in around 2 seconds
- 453k rows can be filtered in under 2 seconds
- Distinct keys can be generated in seconds
- Yet grouping the same 453k rows takes over 3 minutes
This suggests the problem is specifically related to how the engine is handling Table.Group rather than the volume or shape of the data itself.
Hi DataGirl
Have you had a chance to look through the responses shared earlier? If anything is still unclear, we’ll be happy to provide additional support.Hi DataGirl
We wanted to follow up to check if you’ve had an opportunity to review the previous responses. If you require further assistance, please don’t hesitate to let us know.
