Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
sjhand
Frequent Visitor

Issues pulling General Ledger Entries table from Business Central into PowerBI

Hi there,

 

We have the online version of Business Central for our Finance department. I have been having issues pulling the General Ledger Entries table into PowerBI. I am sort of new to the Power BI world and learning most of the concepts pretty quickly.

 

If I go into the 'General Ledger Entries' section within Business Central and extract the data through "Edit in Excel", the file that is saved has a live connection into the same table and the data pull is instantaneous. My understanding is that this creates an Odata connection through a Dynamics Excel add-in app. It pulls all the life-to-date data in an instant without issues.

 

But in Power BI, regardless of whether I am using Odata (Webservices) or API 2.0 to pull the exact same data, the pull takes forever. It sometimes pulls 100s of megabytes of data , which seems unnecessary. Sometimes at the end of the query I just get a blank screen as if no data has been retrieved. I am adding as many filters (year, account #) as I can to limit the query.

All other tables work just fine.

 

My issue seems similar to  https://community.dynamics.com/business/f/dynamics-365-business-central-forum/428452/odata-connecti..., which went unanswered.

 

How can I resolve this? I just want to be able to get the data to create some custom reports. Is this https://docs.microsoft.com/en-us/dynamics365/business-central/dev-itpro/administration/operational-l... pointing to some kind of limitation?

3 REPLIES 3
AspasiaLiz
Regular Visitor

I have very similar issue - so slow- did you get any solution?

sjhand
Frequent Visitor

Hello,

I am using both Web Services Odata 4.0 and API 2.0, and both are giving me issues.

 

The "edit in excel" option in Business Central basically saves a file that is directly connected to the table that I am viewing through Odata 4.0. I can refresh this file and call the latest data for reporting through the file. This particular file is giving me no issues and its the exact same data that I am trying to access directly through Power BI. The file itself with the data is 2.5megs, and PowerBI is calling anywhere from 150-200megs from the same table.

 

I've read somewhere on this forum that I should be adding a filter to my query to reduce the amount of data that I am calling. Would this help in my situation?

 

For now, I am using the "edit in excel" files as an intermediary step to build my logic in Power BI.

v-shex-msft
Community Support
Community Support

HI @sjhand,

What type of data connector are you working on? Did the 'edit in excel' file already saved to your Business Central? Please share some more detail to help us clarify your scenario.

How to Get Your Question Answered Quickly 

In addition, I don' think power bi will keep live connected to the files, these data tables will be updated when you use refreh feataure to upate them.

Regards,

Xiaoxin Sheng

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.