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