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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Hannisky
Frequent Visitor

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
Employee
Employee

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
Employee
Employee

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


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

Daryl-Lynch-Bzy
Resident Rockstar
Resident Rockstar

@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

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

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

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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

Top Solution Authors
Top Kudoed Authors