I posted a similar question 2 months ago and got responses, but no solutions.
We had 2 Business Central companies where we're linking GL Entries tables to Excel via Power Query with OData feeds (one for each company). This is not Power BI, we're just using Power Query to bring data into Excel. These queries used to update just fine with these two companies, but ever since adding a third company, the queries throw this error:
/OData: Unable to read data from the transport connection: an existing connection was forcibly closed by the remote host/
The queries update within the Power Query editor without error, but they do occasionally take longer than normal. However, the table in the worksheet won't update and always throws this error. From previous posts on this issue, it sounds like it's too much for the query to process. I've already tried splitting the queries into smaller pieces, keeping them all together for processing, loading to separate tables first, etc. and nothing works. It seems like a Microsoft Excel insufficiency, but if there's something else we can do to get this to work, I'm all ears.
More on the queries themselves: I have 3 identical queries (only difference is the OData url to point to the respective company). These queries pull in all data and immediately filter the data. It filters down to only about 5-15 lines per company. I then have a /Master/ query that appends the 3 together. I can load any one of these /single-company/ queries into Excel tabs (including loading all data prior to filtering). I can also update the /Master/ query in the Power Query editor. However, when I try to load the /Master/ query result into an Excel tab, it throws this error every time. Even if I refresh each query individually, load it to a tab, and then source the /Master/ from those tabs (rather than from the query results directly), I still get the error. I almost think Power Query just doesn't have the bandwidth, but then again, it works in the editor. Why would this not work in the tab? Better yet, how do I get it to work in the tab?
Thanks,
Jared
