build source file path through multiple parameters?

[jbaich]

Hi all, I'm trying to learn how i might be able to use parameters to build my source file paths for a variety of queries using Power Query...

I think i'm most of the way there, but getting tripped up on the last one, which is a query type parameter that i want to generate a list of values (folder names), that i can then select the appropriate parameter value from that list for the query I am working on... so basically the end result would be the equivalent of:

Source = (Parameter 1 & Parameter 2 & Parameter 3 & Parameter 4)

I'm getting the

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

error and I've read Ken Puls blog that many posts, articles and comments seem to reference in order to get around this, but i'm not sure i'm following or at least how to apply to my situation, which is this...

I want to use parameters to build the source file paths for my queries. Each parameter represents a different part of the path so that when they are consolidated, i have a complete path.

The plan was to have 4 parameters, where the last would be based off a query that would generate a list of all the subfolders in the parent folder as designated by the previous (3rd) parameter. I've got the first 3 working together nicely, but when i add in the 4th i get the firewall message.

The fourth parameter is based off a query, which is then converted into a list, which i select as the basis for the parameter.


So in my parameter manager I have:

Required = False
Type = Any
Suggested Values = Query
Query = Data Source Folder (this is the list created from the original query to get subfolder names)
Current Value = one of the subfolder names from the list

I tried converting the code for the list query from the original:

let    Source = Folder.Files("\\H:\Desktop\2019\Extracts"),
    #"Removed Other Columns" = Table.SelectColumns(Source,{"Folder Path"}),
    #"Extracted Text Between Delimiters" = Table.TransformColumns(#"Removed Other Columns", {{"Folder Path", each Text.BetweenDelimiters(_, "Extracts\", "\"), type text}}),
    #"Removed Duplicates" = Table.Distinct(#"Extracted Text Between Delimiters"),
    #"Renamed Columns" = Table.RenameColumns(#"Removed Duplicates",{{"Folder Path", "Data Source Folder"}}),
    #"Data Source Folder1" = #"Renamed Columns"[Data Source Folder]
in
    #"Data Source Folder1"

To the following, by referencing the original query to create a new list (code below) and reassigned this query to the parameter, but that didn't seem to change anything...

let    Source = #"Extracts Subfolders",
    #"Data Source Folder1" = Source[Data Source Folder]
in
    #"Data Source Folder1"

As always, any help would be very much appreciated!

Thanks

 

[jbaich]

Nevermind, please ignore this post, i'd edit or delete it if i could

Nevermind, please ignore this post, i'd edit or delete it if i could