Power Query find Parent Account in Chart of Accounts

[samantha75]

Hi All

I'm struggling to find a formula to generate the parent account column for the account No in chart of accounts. The format is as below and the last column (Parent_Account) is what is need the system to generate. Sample file is attached with the expected values for the parent account. Here's the link to Ondedrive

https://1drv.ms/x/s!AiMefkhZjs81ges8OrBe3O8yHjFjGw?e=arK9jP

[samantha75]

Has anyone got a suggestion to get the parent account? The human eye can detect the pattern here, but can Power Query be programmed?

[samantha75]

It's a hierachy 

[v-cgao-msft]

Hi @samantha75 ,

You can explain better the logic behind your requirement. Please elaborate how you came u with the values on your [Parent_Account] column.

Best Regards,
Gao
Community Support Team

If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly. If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

How to get your questions answered quickly --  How to provide sample data in the Power BI Forum

[samantha75]

Ok I'll try to explain..

The accounts are nested in Begin-End hierarchy

100 Begin 

     200 Begin 

            300 Begin 

                    400 Post ACC 

            End

     End

End

The task is to find the account number - (column 1) from the begin entry 1 level above. so in above example for the black post account number it is the red begin account number = 300, for the red it is the green begin account = 200