Reply
Hannisky
Frequent Visitor
Partially syndicated - Outbound

Getting chart of accounts from column reference

Hi all. Hoping for som help with a problem i've run into. I have the below a chart of accounts that has been run through a function that splits the column _acc into respective numbers (using a function created by @ImkeF ). So all accounts are 4 digit numbers. All other numbers are referens to _RowNo. Here's the issue... the reference to _RowNo in _ListAcc can either be to a series of accounts or to _RowNo. Eg. 111000 references accounts 3051 to 3058 (ignore the duplicated 3054 it's a typo) but 120000 references 111000. 

The result should be only the referenced list of 4 digit accounts ie 120000 would also be 3051 to 3058. A column with lists of accounts would be ideal.  

Hannisky_0-1646122424386.png

 

 My plan was to filter out the parent 'accounts' to a list and first do a check if there is a match in _RowNo and resolve these first until there's only references to a list of accounts. But this is as far as i got with my knowledge of PQ. 

Hannisky_1-1646123452995.png

Any help would be appretiated. 

 

I've added an example pbix file via link.

https://1drv.ms/u/s!Ajc6i_FeHBufnQ64tjE_xfskXFla?e=Sq0sLZ

1 ACCEPTED SOLUTION
mahoneypat
Microsoft Employee
Microsoft Employee

Syndicated - Outbound

I couldn't modify your query (as it points to a local file), but you can use the function in this article to get PATH functionality in PQ.

Guest Post: Using List.Accumulate for Input/Output Genealogy – The BIccountant

 

Pat

 





Did I answer your question? Mark my post as a solution! Kudos are also appreciated!

To learn more about Power BI, follow me on Twitter or subscribe on YouTube.


@mahoneypa HoosierBI on YouTube


View solution in original post

5 REPLIES 5
mahoneypat
Microsoft Employee
Microsoft Employee

Syndicated - Outbound

I couldn't modify your query (as it points to a local file), but you can use the function in this article to get PATH functionality in PQ.

Guest Post: Using List.Accumulate for Input/Output Genealogy – The BIccountant

 

Pat

 





Did I answer your question? Mark my post as a solution! Kudos are also appreciated!

To learn more about Power BI, follow me on Twitter or subscribe on YouTube.


@mahoneypa HoosierBI on YouTube


Syndicated - Outbound

Thanks! After a bit of testing and tweaking your script worked just as required.  

Daryl-Lynch-Bzy
Resident Rockstar
Resident Rockstar

Syndicated - Outbound

@Hannisky I was wondering if you were trying to achieve the following outcome based on the following DAX concept:  

PATH – DAX Guide
SQLBI Video

AccountKeyParentKeyAccount PathPath Length
3051111000140000 | 120000 | 111000 | 30514
3052111000140000 | 120000 | 111000 | 30524
3053111000140000 | 120000 | 111000 | 30534
3054111000140000 | 120000 | 111000 | 30544
3055111000140000 | 120000 | 111000 | 30554
3056111000140000 | 120000 | 111000 | 30564
3057111000140000 | 120000 | 111000 | 30574
3058111000140000 | 120000 | 111000 | 30584
4051130000140000 | 130000 | 40513
4052130000140000 | 130000 | 40523
4053130000140000 | 130000 | 40533
4054130000140000 | 130000 | 40543
4055130000140000 | 130000 | 40553
111000120000140000 | 120000 | 1110003
120000140000140000 | 1200002
130000140000140000 | 1300002
140000 1400001

Syndicated - Outbound

Thanks for the vids they were interesting. I would like to resolve this using power query. 

Syndicated - Outbound

Hi no not really. Eg. 140000 should be expanded so that in column _ListAcc shows only accounts (4 digit numbers) for this we need to resolve that 120000 references 111000 and 112000 and not accounts. 

avatar user

Helpful resources

Announcements
March PBI video - carousel

Power BI Monthly Update - March 2025

Check out the March 2025 Power BI update to learn about new features.

March2025 Carousel

Fabric Community Update - March 2025

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

Top Solution Authors (Last Month)
Top Kudoed Authors (Last Month)