- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

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.
https://1drv.ms/u/s!Ajc6i_FeHBufnQ64tjE_xfskXFla?e=Sq0sLZ
Solved! Go to Solution.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

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
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

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
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

Thanks! After a bit of testing and tweaking your script worked just as required.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

@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 |
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

Thanks for the vids they were interesting. I would like to resolve this using power query.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

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.

Helpful resources
Subject | Author | Posted | |
---|---|---|---|
08-29-2024 06:31 PM | |||
09-12-2024 05:30 PM | |||
08-01-2024 02:27 AM | |||
10-16-2024 02:26 AM | |||
08-14-2024 07:26 AM |
User | Count |
---|---|
32 | |
19 | |
14 | |
11 | |
10 |