Hello,
I am trying to update the source of my report based on a parameter. I can successfully update it using a List of values parameter: I can specify paths for my query and set the parameter name as File Path for the query. Works great.
This is how the Advanced editor code looks like:
let
Source = Csv.Document(File.Contents(StaticList),[Delimiter=",", Columns=14, Encoding=1252, QuoteStyle=QuoteStyle.None]),
#"Promoted Headers" = Table.PromoteHeaders(Source, [PromoteAllScalars=true]),
#"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"Field1", Int64.Type}, {"Field2", Int64.Type}...})
in
#"Changed Type"
This is what the Parameter code looks like:
"\\files\file1.csv" meta [IsParameterQuery=true,
List={"\\files\file1csv", "\\files\file2.csv"},
DefaultValue="\\files\file1.csv", Type="Any", IsParameterQueryRequired=true]
Now I am trying to do the same but using a parameter with Suggested Values of type Query: this will call a database to get the list of options. The parameter works well, this is the code:
"\\files\file1.csv" meta [IsParameterQuery=true, ExpressionIdentifier=path, Type="Text", IsParameterQueryRequired=true]
In the above, path is a query
let
Source = Sql.Database("ServerName", "DatabaseName", [Query="SELECT * FROM Paths"]),
path1 = Source[path]
in
path1
Unfortunately when I try to use it in the source of my query I get this error:
Formula.Firewall: Query 'Output' (step 'Changed Type') references other queries or steps, so it may not directly access a data source. Please rebuild this data combination.
This how the Advanced Editor code looks like:
let
Source = Csv.Document(File.Contents(DynamicList),[Delimiter=",", Columns=14, Encoding=1252, QuoteStyle=QuoteStyle.None]),
#"Promoted Headers" = Table.PromoteHeaders(Source, [PromoteAllScalars=true]),
#"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"Field1", Int64.Type}, {"Field2", Int64.Type}...})
in
#"Changed Type"
Is there a way I can get this working?
Thank you.
