Forum Discussion
What leads to bloat?
I have a PowerQuery script that is working fine. It takes 10-15s to execute. The indicator at the bottom right of the screen gets up to about 3 MB from the largest file, before execution ends. There are three CSV files involved, of approx 3 MB, 94 MB and 54MB.
The following statement expands a left join to aggregate a date field and select the max value.
= Table.AggregateTableColumn(#"Join with EventsOfEventTypeAssessment", "EventsOfEventTypeAssessment", {{"EventDate", List.Max, "LastAssessmentDate"}})
When I add that statement at the end of the script, it takes a long time to run. It's been going for more than ten minutes with no end in sight:
That CSV file is only 94 MB in size, so I don't really understand how PowerQuery has managed to extract 9.01 GB from it, and it's still not finished.
What might have led to this happening? I can't think where to start debugging it.
In case it helps, what I'm trying to achieve would be the PowerQuery equivalent of:
SELECT Parent.ID, Max(Child.SomeDate)
FROM Parent LEFT JOIN Child ON Parent.ID = Child.ParentID
GROUP BY Parent.ID
1 Reply
- AlexisOlson
Super User
Working with text sources can be inefficient (especially when doing joins) since they aren't stored in a way that's efficient for querying.
If the text sources are reasonably sized (as yours are), I'd recommend buffering them into memory before doing joins and aggregations. Check the links in this prior post for more detail on buffering:
https://community.powerbi.com/t5/Power-Query/Speeding-up-power-query-Table-Buffer/td-p/640595
