Getting chart of accounts from column reference

[Hannisky]

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.  

 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. 

Any help would be appretiated. 

I've added an example pbix file via link.

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

[mahoneypat · Accepted solution]

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

[Hannisky]

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

[Daryl-Lynch-Bzy]

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

PATH – DAX Guide
SQLBI Video

AccountKey	ParentKey	Account Path	Path Length
3051	111000	140000 | 120000 | 111000 | 3051	4
3052	111000	140000 | 120000 | 111000 | 3052	4
3053	111000	140000 | 120000 | 111000 | 3053	4
3054	111000	140000 | 120000 | 111000 | 3054	4
3055	111000	140000 | 120000 | 111000 | 3055	4
3056	111000	140000 | 120000 | 111000 | 3056	4
3057	111000	140000 | 120000 | 111000 | 3057	4
3058	111000	140000 | 120000 | 111000 | 3058	4
4051	130000	140000 | 130000 | 4051	3
4052	130000	140000 | 130000 | 4052	3
4053	130000	140000 | 130000 | 4053	3
4054	130000	140000 | 130000 | 4054	3
4055	130000	140000 | 130000 | 4055	3
111000	120000	140000 | 120000 | 111000	3
120000	140000	140000 | 120000	2
130000	140000	140000 | 130000	2
140000		140000	1

[Hannisky]

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

[Hannisky]

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.