Cyclic reference between fields of the same record

7ohm7

New Member
Joined
Oct 31, 2019
Messages
9
Dear friends and Excel fans,

I would like to ask you for advice.

I use PowerQuery and I use quite a lot of queries (300+) - they are divided according to the systems they help and the methods they use. I have been thinking for a long time how to organize them well and I started using the same principle as #shared i.e. record of objects/functions/queries. I wanted to create a hierarchical queries tree that would always be included in a record - to simulate the hierarchical benefits of object-oriented languages - the best example:
- one creates a QueryName and can refer to it simply as "QueryName" or as "#shared[QueryName]" - but if he wants 300 of them, he must simply have 300 queries in excel/powerbi
- I want to use 300 queries but only import packages (classes) - e.g. import the package "SystemName" and then refer to the individual queries e.g. via "SystemName[QueryGroupName][QueryName]"

I am satisfied with this idea, but I just can't overcome the problem with cyclic references.

If the user wants to refer from Query1 to Query2, he only writes the name "Query2" to Query1 or without shortcuts more accurate reference to #shared[Query2] from #shared[Query1], i. from one field to another in same record. No problem.

I also want to refer one field in another in same record. I would like to refer to SystemName[QueryGroup2][Query] from SystemName[QueryGroup1][Query]. But this will cause a cyclic reference error. It will probably be necessary to add that I have each QueryGroup source code in a separate text file which PQ evaluates via Expression.Evaluate, so I need absolute path to target query/field.

Yes, I understand that to access SystemName[QueryGroup2][Query], the powerquery needs to execute whole SystemName, and that's where the reference is called - but why isn't there such a problem in the #shared record?
How to achieve similar immunity against this error? After all, PQ has a lazy evaluation, so it doesn't do anything it doesn't need - so why it needs to execute caller SystemName[QueryGroup1][Query] when all he needs from SystemName is called target SystemName[QueryGroup2][Query]?


Thanks a lot.

Marek
 

Some videos you may like

Excel Facts

Last used cell?
Press Ctrl+End to move to what Excel thinks is the last used cell.

Watch MrExcel Video

Forum statistics

Threads
1,127,554
Messages
5,625,474
Members
416,109
Latest member
TripleA00123

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top