cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
nova
Frequent Visitor

Power BI and Business Central

Dear all,

 

I am trying to create a balance sheet as well as an income sheet in Power BI with data coming from Business Central. I am currently using Query 17 as my basic account list (as it contains information about totalling etc.) and am looking for a simple way to get the parent account key for every account so that I can then create the path (as we're dealing with ragged hierarchies) and use this in my matrices. 

Obviously, I can enter the parent account for every account myself. However, this solution is not really future-proof as new accounts might be opened. 

Any inputs on a simple way to figure out the parent account key and other hints are appreciated.

 

Thanks in advance

4 REPLIES 4
Fowmy
Super User
Super User

@nova 

How about connecting to the Chart of Accounts Table Table # 15 as the dimension of your account then connecting to Table # 17 which is the GL Entries?

For the BS layout, you design it in an Accounts Schedule and link 





Did I answer your question? Mark my post as a solution! and hit thumbs up


Subscribe and learn Power BI from these videos

Website LinkedIn PBI User Group

nova
Frequent Visitor

Hi @Fowmy 
Thanks for your reply. Unfortunately, I am not able to publish page 15 as it doesn't seem to exist (well I can choose it at first, but then it changes to page 14)...

@nova 

I believe you are trying to connect to BC, you need to should be able to see the Table objects.  I did not get it when you mentioned pages here. 

Did I answer your question? Mark my post as a solution! and hit thumbs up


Subscribe and learn Power BI from these videos

Website LinkedIn PBI User Group

nova
Frequent Visitor

@Fowmy

In order to understand what the different pages and queries represent, I logged into BC. Then you can publish web services either as query or as pages (see https://docs.microsoft.com/en-us/dynamics365/business-central/dev-itpro/webservices/publish-web-serv...) . 

Object-ID 15 is missing, we have 5 and the next one is 16 and if I want to publish a new one as web service (15), it doesn't work. 

Since Power BI and BC are issued by Microsoft, I assumed there would be a simple solution to get the necessary information.

Helpful resources

Announcements
May 23 2022 epsiode 5 without aka link.jpg

The Power BI Community Show

Welcome to the Power BI Community Show! Jeroen ter Heerdt talks about the importance of Data Modeling.

charticulator_carousel_with_text (1).png

Charticulator Design Challenge

Put your data visualization and design skills to the test! This exciting challenge is happening now through May 31st!

Power BI Dev Camp Session 22 with aka link 768x460.jpg

Check it out!

Mark your calendars and join us on Thursday, May 26 at 11a PDT for a great session with Ted Pattison!

Power BI Release May 2022 768x460.png

Check it out!

Click here to read more about the May 2022 updates!