Forum Discussion
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.
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.
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
5 Replies
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
Thanks! After a bit of testing and tweaking your script worked just as required.
Hannisky I was wondering if you were trying to achieve the following outcome based on the following DAX concept:
PATH – DAX Guide
SQLBI VideoAccountKey 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 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.
Thanks for the vids they were interesting. I would like to resolve this using power query.
