Formula to find parent accout in indented chart - Business Central

[samantha75]

Hi All

Business Central Chart of Accounts does not the parent account stored and it is based on indentation as below. Is there a way to to find the parent account for each of the accounts using M or DAX? Or any other suggestions. The idea is to create a hierarchy in PBI with groups 

[v-yalanwu-msft · Accepted solution]

Hi, @samantha75 ;

You could try it in power query:

1.add condition column.

2.add funtion.

= Table.Group(#"Added Conditional Column", {"Custom"}, {{"Index", each Table.AddIndexColumn(_, "Index", 1, 1), type table}})

3.add custom culumn.

=if [Custom] ="" then null else [Custom]&Text.From( [Index.1])

4.fill down and add another custom column.

=if [Custom] ="" then [Custom.1]&"-"&Text.From([Index.1]) else [Custom.1]

The final show:

Or you could  use matrix visual.

Best Regards,
Community Support Team _ Yalan Wu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.