topic Re: Power Query find Parent Account in Chart of Accounts in Power Query

Power Query find Parent Account in Chart of Accounts

samantha75 — Wed, 28 Dec 2022 20:56:45 GMT

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

Re: Power Query find Parent Account in Chart of Accounts

Anonymous — Thu, 29 Dec 2022 03:00:08 GMT

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

Re: Power Query find Parent Account in Chart of Accounts

samantha75 — Thu, 29 Dec 2022 08:34:16 GMT

It's a hierachy

Re: Power Query find Parent Account in Chart of Accounts

samantha75 — Mon, 02 Jan 2023 21:12:19 GMT

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

Re: Power Query find Parent Account in Chart of Accounts

samantha75 — Tue, 03 Jan 2023 21:34:18 GMT

Ok I'll try to explain..

The accounts are nested in Begin-End hierarchy

100 Begin

200 Begin

300 Begin

400 Post ACC

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