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

Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now

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
Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.

Nov PBI Update Carousel

Power BI Monthly Update - November 2024

Check out the November 2024 Power BI update to learn about new features.

October NL Carousel

Fabric Community Update - October 2024

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