Skip to main content

Notifications

Announcements

Join us in the Community for an AMA: December 12th Register Today
News and Announcements icon
Dynamics 365 Community / Forums / Small and medium business | Business Central, N... / Business Central OData...
Small and medium business | Business Central, N...
Suggested answer

Business Central OData Power Query Error

(1) ShareShare
ReportReport
Posted on by Jared M 40
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
  • Jared M Profile Picture
    Jared M 40 on at
    Business Central OData Power Query Error
    Thanks for the responses.
     
    Zhu, a few follow up questions: How do I determine if I've reached any of the request limits? I see the limitations listed in your post (and on the Microsoft page), but where can I find those specs for my query? I'm not sure where to look to see where I'm at on those metrics. Second question, how would I go about submitting an SR to Microsoft? Is that through a partner portal somewhere, or where would I find information on that? Sorry for the basic questions--I'm still a beginner :)
     
    Thanks,
     
    Jared 
  • Suggested answer
    Judy Profile Picture
    Judy Microsoft Employee on at
    Business Central OData Power Query Error
    Hi,
     
    There can be a variety of reasons for this error, and if the report is linked to multiple other tables,
    this can lead to slowness, which can lead to this type of issue.
     
    Best Regards,
    Judy
  • Suggested answer
    YUN ZHU Profile Picture
    YUN ZHU 73,333 Super User 2024 Season 2 on at
    Business Central OData Power Query Error
    Hi, first you need to rule out whether the OData request limits have been reached.
    And I'm sorry that I can't help you directly, but for such specific standard issues, it is recommended that you submit an SR to the Microsoft support team and let them assist in the investigation.
     
    Hopefully other experts can give you better advice.
     
    Thanks.
    ZHU

Under review

Thank you for your reply! To ensure a great experience for everyone, your content is awaiting approval by our Community Managers. Please check back later.

Helpful resources

News and Announcements

Join us in the Community for an AMA: December 12th Register Today

Quick Links

December Spotlight Star - Muhammad Affan

Congratulations to a top community star!

Community AMA December 12th

Join us as we continue to demystify the Dynamics 365 Contact Center

New! Quick response templatesâš¡

Save time with the new custom templates!

Leaderboard

#1
André Arnaud de Calavon Profile Picture

André Arnaud de Cal... 291,219 Super User 2024 Season 2

#2
Martin Dráb Profile Picture

Martin Dráb 230,056 Most Valuable Professional

#3
nmaenpaa Profile Picture

nmaenpaa 101,156

Leaderboard

Featured topics

Product updates

Dynamics 365 release plans