Forum Discussion
RAM Problems -Excel Crashes even with a very small DataModel
Hi gehe_muc ,
You're getting into the programming architecture of how Power Query technically works, which is way beyond my skillset I'm afraid.
However, I notice that you mentioned that you "transfer and merge 3-4 times". As I said before, merges are very expensive and should be avoided, especially if you're limited to 32bit RAM limitations. There are a number of alternatives/mitigations to merge costs which I'm happy to explore with you if required.
Pete
Hi Pete,
thanks for trying to solve my issue! I'm german so I'm not sure what you mean bei "merge". In the Main-Query I do 4 "Joins" and adding columns and filtering after. Maybe I have to try another kind of transformation...
I'm curious about alternatives to solve my problems, thank you for sharing!
Steve
Hi gehe_muc ,
'Merges' ar what 'joins' are called in Power Query, and they're very intensive on system resources. Four merges/joins on one table could quite easily cause you major issues.
The primary way to avoid merges is to recreate them in the data model using relationships instead. Where you might do a left outer merge on your fact table to bring in item description from a dimension table, you would recreate this using a relationship like this:
factTable[itemID] MANY : ONE dimensionTable[itemID]. You would then use the dimensionTable[itemDescription] in any visuals where you want to display it.
If you're using merges to perform table filtering operations, there are other ways around this using list references and buffering, but I suspect it's my first example that you're actually using the merges for.
Pete