Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
samantha75
Frequent Visitor

Power Query find Parent Account in Chart of Accounts

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

 

chart.png

 

4 REPLIES 4
samantha75
Frequent Visitor

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
Frequent Visitor

It's a hierachy 

coa 1.png

v-cgao-msft
Community Support
Community Support

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

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

COA hierarchy.png

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors