Referencing two queries

I'm required to have a query referencing two other queries (in order to workaround the 'please rebuild' privacy error message).
One query is a table. The second query is only for fetching a named range value from the workbook within PQ.
The new query would manipulate the table with the named range value.

How can we reference two queries?

Hey Michlus,

Basically we pull both sets of data into separate queries. Once done we reference one in a new query, then inject a step in that query to call the other.

In the case of the attached, I did this:

• Created a table of products and loaded it into a connection only query
• Created a named range with one of the products in it. I loaded that to a connection only query, and drilled in to the only value

That gave me the two pieces I need. I then created a new query by the following steps:

• Right click the Products query and choose Reference
• Go into the Advanced Editor and insert the following line to call the data from the 2nd query
• Code:

  myFilter = rngFilter,

• I then added a step to filter the product table, and replaced the hard coded value of "apples" with myFilter

Hopefully this helps, but if you'd like to get help with your code, then please click the "Go Advanced" button and upload a copy of your workbook if you can.

Hey Ken,
I think I got it! Thank you for your assistance!
If I understand correctly, this means that since I have several named ranges values I need to load, I'd need separate queries, one for each named range. Correct? Not so 'clean' ...
Would it help to create a sort of hidden table in excel with a copy of the named range values, i.e will it work to load this table in a query, and then have the new query load the first table (by refer) and then also refer to the separate drilled values?
Any other option you have in mind?
Alternatively, of course, it's also possible to change the privacy setting ... not recommended I guess.
Thank you so much!

Okay, so sort of yes, sort of no.

In the solution I cooked up, we only pulled in one value, so I made one query for that. Based on it being from a named range, this is the method we'd need to use.

We can get around that using a parameter table/query though. What you'd want to do is:

1. Create a separate table in the workbook called "Parameters". Make sure the first column is called "Parameter" and the second column is called "Value".
2. Give each parameter a name in the first column, then link to the named range in the second column.
3. Create a "Connection only" table that points to the Parameters table

This creates a read only view of the table, and will allow us to bypass the formula firewall for future steps.

Next we copy in the fxGetParameter function.

Finally, we call the fxGetParameter function in our loader query.

I've attached an updated sample that shows how you can do that, but it involves adding a separate function query, then calling it as needed. The key here is going table --> function --> query to avoid the formula firewall.

Genius!
Thank you!!