How to create hierarchy for Chart of Accounts

[hkusulja]

Hello,

I am using latest Power BI Desktop and have Power Query returning the data from table with all G/L Accounts (aka Chart of Accounts). (I am using Dynamics 365 Business Central, v20 SaaS, but it can also be Excel as a source)

I need help to create standard accounting perspective for hierarchy on Chart of Accounts so it can be used within Power BI report as standard hierarchy.

The hierarchy should be based on row account type (posting, heading, begin-total, end-total) and totaling info.

Data is simple, example:

No.	Name	Account Type	Totaling
10000	BALANCE SHEET	Heading
10001	ASSETS	Begin-Total
10100	Checking account	Posting
10110	Mercury - Checking - Main	Posting
10200	Saving account	Posting
10210	Mercury - Savings - Main	Posting
10300	Cash	Posting
10400	Accounts Receivable domestic	Posting
10410	Accounts Receivable foreign	Posting
10500	Prepaid Expenses	Posting
10550	Freight expense	Posting
10700	Inventory merchandise	Posting
10750	Inventory resale service	Posting
10770	Inventory material	Posting
10800	Equipment	Posting
10900	Accumulated Depreciation	Posting
10990	TOTAL ASSETS	End-Total	10001..10990
20001	LIABILITIES	Begin-Total
20100	Accounts Payable domestic	Posting
20110	Accounts Payable foreign	Posting
20200	Purchase Discounts	Posting
20300	Purchase Returns & Allowances	Posting
20400	Deferred Revenue	Posting
20500	Credit Cards	Posting
20550	Loan from shareholders	Posting
20570	Franchise Tax Fee Liability	Posting
20600	Sales Tax Payable	Posting
20650	Use Tax Payable (Reverse Charge Purchases)	Posting
20700	Accrued Salaries & Wages	Posting
20800	Federal Withholding Payable	Posting
20900	State Withholding Payable	Posting
21000	FICA Payable	Posting
21100	Medicare Payable	Posting
21200	FUTA Payable	Posting
21300	SUTA Payable	Posting
21400	Employee Benefits Payable	Posting
21500	Vacation Compensation Payable	Posting
21550	Employees Payable	Posting
21600	Garnishment Payable	Posting
21700	Federal Income Taxes Payable	Posting
21800	State Income Tax Payable	Posting
21900	Notes Payable	Posting
30100	Capital Stock	Posting
30200	Retained Earnings	Posting
30290	This Year Earnings	Total	40000..69995
30300	Distributions to Shareholders	Posting
30990	TOTAL LIABILITIES	End-Total	20001..30990

Or, if this is not possible based on Totaling type, then what about 1 expands/drill downs all 10..19 , and then 100.. expands all 100., however not G/L Accounts have the same number of characters/numbers...

[hkusulja]

I belive I need to create using Power Query (M langage) additional columns, so I can make level 1, level 2 and level 3 columns.

Does needs to be calculated based on No. column. example:

L1catetogory = select first character substr(0,1) , and filter whole table, sort a-z, and then select first (name) value

L1catetogory = select first 2 characters substr(0,2) , and filter whole table, sort a-z, and then select first (name) value

then i will be able to use create hierarchy correctly.

I want resolution in Power Query and not DAX due to other reasons..

[v-jianboli-msft]

Hi @hkusulja ,

Sorry for that the information you have provided is not making the problem clear to me. Can you please share more details to help us clarify your scenario? Please provide more details about the results you want, such as how does the hierarchy look like.

Besides, you can create hierarchy in the field pane:

Here are some documents that may helps you, please refer to:

What a Power BI Hierarchy Is, and How to Use it? - RADACAD

Power BI Hierarchy: How-to Create Hierarchy in Power BI | Stoneridge Software

Create a matrix visual in Power BI - Power BI | Microsoft Docs

Add multiple fields to a hierarchy slicer - Power BI | Microsoft Docs

Best Regards,

Jianbo Li

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.