Unable to merge queries : server sent an unrecognizable response

[Anonymous]

Hi,

Using Excel 365 for enterprise, semi-annual channel.

Problem: I'm pulling 5 tables with max 190K rows. If I try to merge them within Power Query I get the following error:

All tables individually load without a problem if loaded to a Table, the data model or whatever choice I make.

Looking around this seems to be the result of 2 combos: tables being big and mashup container being limited to 256MB in Power Query for Excel (which, at the opposite of Power Query for PBI, can't be changed).

Temporary work around: load the 5 tables into an Excel sheet, load them into Power query (even if they're already there 😞 ) and merge them. This avoids Power Query from filling up memory usage (one of the tables contains multiple group by/pivot/unpivot).

If anyone has any idea on how to get that done exclusively in Power Query that would prevent me from having a complex gigantic file just to merge a few tables.


Thx!
Doesn anyone know how t

[BA_Pete]

Hi @Anonymous ,

Is it feasible not to do the merges at all and instead relate the tables using the Excel Data Model?

Pete

[Anonymous]

I'm a big fan of Power Pivot and DAX and that's what I would usually do.

Tried but I fear not:.  If I build a data model with a table containing a list of all 190K accounts and then want to filter those where MyFlag is Yes, I can't do that: MyFlag is yes on all rows of another table containing a subset of the 190K ence its value is always YES (within that table).


And final outcome needs to be a merge of all tables in an Excel sheet because of business requirements (users having to be able to run vlookups because... let's not get into that 🙂 )