web
You’re offline. This is a read only version of the page.
close
Skip to main content

Notifications

Announcements

No record found.

News and Announcements icon
Community site session details

Community site session details

Session Id :
Dynamics 365 Community / Forums / Small and medium business | Business Central, N... / Connecting Business Ce...
Small and medium business | Business Central, N...
Suggested Answer

Connecting Business Central directly to Power Pivot Excel Add-In via OData

(0) ShareShare
ReportReport
Posted on by Madsen1998 5

Hi,

I'm currently looking to integrate Business Central data directly to Power Pivot for Excel usage, without having to go through the ETL functions in PowerBI or Power Query within Excel. 

I managed to connect BC to Excel using this method: https://www.encorebusiness.com/blog/how-to-setup-excel-query-for-dynamics-365-business-central/

However, through this method, I end up having to load all data into my Excel sheet before I can further load it into Power Pivot. 

Now to the case: Power Pivot has the same ETL function as Excel (OData Data Feed) which should be able to load in data from the API function under web services in Business Central (as Power Pivot is not updated anymore, it doesn't have a direct Business Central ETL function). However, every time I insert the API, I get the following error message : The underlying connection was closed: An unexpected error occured on a send.

I think this might be due to a certificate error, but I'm not sure how to fix it. 

Any help would be appreciated

Thanks!

BR,
René

I have the same question (0)
  • Suggested answer
    Marco Mels Profile Picture
    Marco Mels on at

    Hello,

    If you think it may have something to do with the SSL cert, one of the most common issues are related to Tls and the default security protocol. Without having knowledge of the context of your query, the environment, release of BC, there is not much to say about it via community.

    Are you using a self signed cert?

    Thanks.

  • Madsen1998 Profile Picture
    Madsen1998 5 on at

    Hi Marco,

    Not really a tech guru myself, but we're currently in a test environment in Business Central - what I did was to get the API link from the Web Services overview for a random page in BC. The consultancy house assisting us in implementing BC has adviced us to a third-party application to import data from BC to PowerPivot. Our take on it is that it should be possible to import from Microsoft software to Microsoft software, and we can get it to work for Excel directly (Using the Odata ETL function), but this brings the necessity to have all data within the Excel sheet (which can be problematic when having millions of rows).

    My security settings are set like this:

    pastedimage1633593155584v1.png

    I've tried to change these to Basic and inserted m password and user ID, but it doesn't seem to work either. 

    Hope you're able to help despite the limited information :)

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

Quick Links

Responsible AI policies

As AI tools become more common, we’re introducing a Responsible AI Use…

Neeraj Kumar – Community Spotlight

We are honored to recognize Neeraj Kumar as our Community Spotlight honoree for…

Congratulations to the November Top 10 Community Leaders!

These are the community rock stars!

Leaderboard > Small and medium business | Business Central, NAV, RMS

#1
OussamaSabbouh Profile Picture

OussamaSabbouh 2,468

#2
YUN ZHU Profile Picture

YUN ZHU 923 Super User 2025 Season 2

#3
Sumit Singh Profile Picture

Sumit Singh 607

Last 30 days Overall leaderboard

Featured topics

Product updates

Dynamics 365 release plans