Dynamic REST Parameter in Power Query Editor

[gracz]

I have an Excel sheet where a user enters a number into a table which is in turn sucked into Power Query Editor and indexed such that it's a variable I can inject into other queries.

Now that I have my URL parameter set up I can inject it into the main query, which is a REST call, like so:

Or I could get rid of the param altogether and just call the Excel.CurrentWorkbook from within the main query:

Anyway you slice it, it works for me just fine. But fails for anybody I email it to.  It fails with the message:

Formula.Firewall: Query 'Query1' (step 'Added Custom') references other queries or steps, so it may not directly access a data source. Please rebuild this data combination.

I'm unsure of how to inject the param from the excel sheet into the REST query without storing the param inside another query, which is exactly the problem the warning message is stipulating.  I've read other related questions like: 

https://community.powerbi.com/t5/Desktop/Formula-Firewall-Query-references-other-queries-so-it-may-n...

which had the main fix of:

https://www.excelguru.ca/blog/2015/03/11/power-query-errors-please-rebuild-this-data-combination/

Which doesn't apply here since I'm not merging queries, I'm combinging them textually, which really is the crux of the problem.

Any help would be appreciated.

Thanks,

-Matt

Edit: 7/19/21 - Formatting, rephrased a few points for clarity.

[ImkeF]

Hi @gracz ,

I would solve this by turning the firewall off.
This is exactly the case where in my eyes, this is justified.

[gracz]

@ImkeF  I didn't think of that.  And I appreciate the, rare as they may be, justified cases for firewall exceptions and the like.  However, that's not an option as the organizational structures around me won't permit it.

Your solutions implies that there's some property (or properties) of the file such that it doesn't play nice with the firewall when it's passed on to another user.  Do you have an idea of what such properties might be in play here?  For instance, the credentials for the REST API don't change when I pass it on to other users so that's unlikely to be a culprit. If you could identify some possibilities I could then maybe more surgically address the issue in the file, bypassing the need to modify the firewall.  Thoughts?

[ImkeF]

Hi @gracz ,

you can try to convert your URLTable into a function without any arguments (by putting a ()=> in front of it.
Then call it in your main query like so: 
URL = URLTable ()