GL Accounts & GL Entry Relationship - PowerBI

[stunomatic]

I am trying to create a trial balance sheet using PowerBI. (not using content pack as it is not available for NAV2009).


In NAV Chart of Accounts there are Head Accounts and Sub-Accounts.

IN SQL DATABASE Tables
- GL_Accounts. (All Acc. numbers are listed).
- GL Entry. (Only Sub Account numbers are listed because entries are done in sub accounts).


Using relationship 1 to many "GL_Account No"


Now using PowerBI I only want to show Trial Balance of Head Accounts(sum of all sub accounts) and for sub accounts I will use drill down methods BUT I cannot find relation within GL_Entry for head accounts.

In GL_Account table there is one column Totaling in which range of sub accounts is define. 

Please check screenshot. 

[ImkeF]

Hi @PowerChange2021 ,
I would recommend to open a new thread on your specific topic.
For example, currently I don't understand how to determine the head accounts. So it would be ideal if you could provide a sample file (in Excel for example) where you provide sample data of your input structure and the desired output.

[PowerChange2021]

Very good idea! But before that, could you explain how you would do the hierachy in the mentioned step 4:

"4) Expand that column to new rows. This will give you 2 columns for your hierarchy."

Because in step 1) we filter all rows not empty, and thus we do not have names for the columns we end up with?

[ImkeF]

Hello @PowerChange2021 ,
if my understanding is correct, I've covered that scenario in my article here: Create list of accounts from NAV account schedules or COA totaling syntax – The BIccountant

[PowerChange2021]

Hi @ImkeF ,

thanks for the quick reply. Maybe that could help, but I didn't do a great job of explaining my end goal. Maybe that can help:

I wish to create a report (with a table or matrix) that lists all head and sub accounts in one column and then a column that shows the total amount for each account (both head and sub).

Examble: 

I'm really not sure how to get there in terms of how to process the GL_Accounts, so that I can create a relation to the GL_Entry account, and end up with this report. 

I've come so far to create a relation between the two tables (GL_Accounts and GL Entry), which give me the total of every sub accounts, but then to get to it also show the totals for the head account?

I've tried your solution above, but could figure it out, also in terms of the relations between the tables.

Many thanks for helping 🙂 

[PowerChange2021]

Hi @ImkeF and @stunomatic 

I have the exact same issues regarding this in terms of (1) creating a relationship between the two tables, (2) calculating the totalts and (3) visualizing this in a table or matrix:

The two tables are

- GL_Accounts. (All Acc. numbers are listed).
- GL Entry. (Only Sub Account numbers are listed because entries are done in sub accounts).

I have trouble going forward as:

1) In terms of GL_Accounts some of the intervals in the Totalling column have two intervals fx. 11000-11019 and 11025-11030.  How would I manage that? 

2) Could you tell me the next step after your step 4? and how you end up with a table that contains both head accounts and sub accounts? Maybe sharing a dummy file for this?

br

Ralf

[ImkeF · Accepted solution]

1) Filter Column "Totalling" where rows are not empty

2) Add a column "Custom" where you extract start and end value like this: Text.Split([Totalling], "..").

3) Add another column that creates all numbers within that intervall: {Number.From([Custom]{0})..Number.From([Custom]{1})}

4) Expand that column to new rows. This will give you 2 columns for your hierarchy.

[stunomatic]

Thanks it worked 🙂