Hi guys, I've searched and found many threads that seem closely related to my issue, but none exactly addressed my problem. This actually seems simpler than most of the problems I've found, so hopefully many people know what I should do.
I am trying to build a dynamic connection string to an excel workbook in PowerQuery, where the file name in the connection string is supplied with a parameter:
Source = Excel.Workbook(File.Contents("<MyFolderPath>" & Param1), null, true)
This works great if the parameter passes a hard coded string value, but fails as soon as I change it to use a Query/ List as the source. Then, I get this standard error message:
Formula.Firewall: Query 'Query1' (step 'Table') references other queries or steps, so it may not directly access a data source. Please rebuild this data combination.
This problem occurs even when the parameterized query contains nothing but the data connector and the excel sheet selector. I've stripped the M down to the simplest possible code; this is the entire thing:
let
Source = Excel.Workbook(File.Contents("<MyFolderPath>" & Param1), null, true),
Table = Source{[Item="DataSource",Kind="Sheet"]}[Data]
in
Table
For this reason, solutions like the one in this excellent blog post (https://www.excelguru.ca/blog/2015/03/11/power-query-errors-please-rebuild-this-data-combination/) don't seem to apply; I'm only pulling a single data source so there can't be any permissions conflict, and I'm not doing any tranformations that might present challenges for query folding. The data source Permissions setting for all my data sources for this workbook is set to "Public", so even if there were more than one data sourcein this code, permissions conflict shouldn't be the source of this problem.
Here is my complete data flow to create the list I use as the source for the file name parameter.
let
Source = Folder.Files("<MyFolderPath>"),
#"Removed Columns" = Table.RemoveColumns(Source,{"Content", "Extension", "Date accessed", "Date modified", "Attributes", "Date created"}),
#"Filtered Rows" = Table.SelectRows(#"Removed Columns", each Text.StartsWith([Name], "<stuff i need to filter on>")),
List = #"Filtered Rows"[Name]
in
List
Next, I go to Manage Parameters and configure my parameter to use Suggested Values: "Query" and Query: "List" (to get the list resulting from the code above). Then I use the parameter in my connection string as above. That's it.
I can't think of anything else to check besides the things I've done -- radically simplifying the initial query, checking the permissions. What have I missed?
