Forum Discussion
Memory allocation error on DAX table UNION 50M+ rows
Trying a quarterly csv file based data refresh and then doing a DAX table union join of old quarters and new quarters - I now get a memory allocation error.
Queries to the csv files load correctly.
csv files are 2.5GB per quarter , resulting table is 10M rows per quarter. 5 quarters loads OK , 6th quarter creates error when doing DAX union.
This operation worked in April (5 quarters of data / 50M rows) - but now doesn't for the June quarter
32GB RAM worked OK last time , and appears to have capacity when viewing performance test on query stage.
Latest July PBI desktop 64 bit installed (7/12/2017) Version: 2.48.4792.721
Is there any other limitation that could be throwing this error ?
Thanks
6 Replies
It might be a better choice to do your joins/appends within the query editor and not via DAX. The memory usage and compression will be more performant that way.
As for the firewall error, this is a well documented issue.
https://www.excelguru.ca/blog/2015/03/11/power-query-errors-please-rebuild-this-data-combination/
Hi pat_energetics,
You can disable the option: allow data preview to download in the background (highlighted in yellow backgroud). Please review this article.
Memory usage is related dataresouce, the Power Query steps, computer configuration and so on. Please see the following blogs for references.Performance Tip for Power BI; Enable Load Sucks Memory Up.
Best Regards,
AngeliaUnfortunately this tip did not resolve the issue.
Now trying a different order and size of query to see if the issue is in the way in which queries are appended into one before loading.
Any other ideas appreiciated.
It now appears that no matter what combination of appending queries I try , I get the following error ....
Formula.Firewall: Query 'Bid_Vol_History' (step 'Appended Query1') references other queries or steps, so it may not directly access a data source. Please rebuild this data combination.
According the the query dependency view , the sub queries being appended into the final query are correctly linked to their respective (csv file) data source.
