Forum Discussion
Business Central and Power BI loading data
I’m using Business Central Cloud version and Power BI Desktop.
1.When I create report in Power BI from tables like General Ledger Entries on Item Ledger Entries then the performance is very poor.
Can I limit the data before loading them via e.g. Posting Date fields?
I found useful information here powerbi - How to pre filter the data Set before loading all the data in Power BI - Stack Overflow but didn’t manage to use the setup yet.
2.Do you know any detailed description of BC tables available by default through Powe BI e.g. detailed names, fields, relations?
I found only this resource but seems to be very limited and all the tables I need to find manually in Power BI.
If somebody doesn't have knowledge about BC tables it’s challenging to find tables in Power BI (even knowing the table name from BC).
Hi Marcin_,
Thanks for reaching out to the Microsoft fabric community forum.
Yes, you’re right, these tables can get quite large, and loading them into Power BI will lead to poor performance.
While Power BI lets you apply filters in the Power Query Editor (like on Posting Date), these filters typically apply after data is loaded from the source. Unfortunately, the default Business Central OData feeds don’t support query folding well enough to push those filters back to the server. So, filtering in Power BI does not reduce the data actually pulled from BC, it just trims it after it’s already been loaded.
To really limit the data before it gets into Power BI, the best approach is to create a custom API page or query in Business Central. You can design it to expose only the fields you need and include filter parameters like Posting Date. Then, publish that as an OData feed and connect Power BI to it. This gives you true server-side filtering and much better performance.
If you're working in Power BI Service (Pro or Premium), you could also look at Incremental Refresh, but this only works if your data source supports query folding, which again isn’t fully reliable with standard BC OData. Custom APIs are a safer bet.
The internal table structures of BC, can be really tough to identify which table is which when loading them into Power BI.
Microsoft does provide an official list of standard APIs here:
API (v2.0) for Dynamics 365 Business Central - Business Central | Microsoft Learn
But this list doesn’t include all the internal tables like Item Ledger Entry or General Ledger Entry by default, those are typically exposed only if someone has published them through the Web Services page in BC.
If you go to Business Central -> Web Services, you’ll see which tables (pages or queries) are currently published as OData feeds. You can also publish additional ones yourself. That’s the easiest way to make specific BC tables available for Power BI and to see the field names as they’ll appear in your Power BI queries.
If I misunderstand your needs or you still have problems on it, please feel free to let us know.
Best Regards,
Hammad.
Community Support TeamIf this post helps then please mark it as a solution, so that other members find it more quickly.
Thank you.
4 Replies
Hi Marcin_,
Thanks for reaching out to the Microsoft fabric community forum.
Yes, you’re right, these tables can get quite large, and loading them into Power BI will lead to poor performance.
While Power BI lets you apply filters in the Power Query Editor (like on Posting Date), these filters typically apply after data is loaded from the source. Unfortunately, the default Business Central OData feeds don’t support query folding well enough to push those filters back to the server. So, filtering in Power BI does not reduce the data actually pulled from BC, it just trims it after it’s already been loaded.
To really limit the data before it gets into Power BI, the best approach is to create a custom API page or query in Business Central. You can design it to expose only the fields you need and include filter parameters like Posting Date. Then, publish that as an OData feed and connect Power BI to it. This gives you true server-side filtering and much better performance.
If you're working in Power BI Service (Pro or Premium), you could also look at Incremental Refresh, but this only works if your data source supports query folding, which again isn’t fully reliable with standard BC OData. Custom APIs are a safer bet.
The internal table structures of BC, can be really tough to identify which table is which when loading them into Power BI.
Microsoft does provide an official list of standard APIs here:
API (v2.0) for Dynamics 365 Business Central - Business Central | Microsoft Learn
But this list doesn’t include all the internal tables like Item Ledger Entry or General Ledger Entry by default, those are typically exposed only if someone has published them through the Web Services page in BC.
If you go to Business Central -> Web Services, you’ll see which tables (pages or queries) are currently published as OData feeds. You can also publish additional ones yourself. That’s the easiest way to make specific BC tables available for Power BI and to see the field names as they’ll appear in your Power BI queries.
If I misunderstand your needs or you still have problems on it, please feel free to let us know.
Best Regards,
Hammad.
Community Support TeamIf this post helps then please mark it as a solution, so that other members find it more quickly.
Thank you.
Hi Marcin_,
As we haven’t heard back from you, so just following up to our previous message. I'd like to confirm if you've successfully resolved this issue or if you need further help.
If yes, you are welcome to share your workaround and mark it as a solution so that other users can benefit as well. If you find a reply particularly helpful to you, you can also mark it as a solution.
If you still have any questions or need more support, please feel free to let us know. We are more than happy to continue to help you.
Thank you for your patience and look forward to hearing from you.Hi Marcin_,
I wanted to check if you had the opportunity to review the information provided. Please feel free to contact us if you have any further questions. If my response has addressed your query, please accept it as a solution so that other community members can find it easily.
Thank you.