Forum Discussion
Create dynamic Power Query SQL by using Excel table column?
Is it possible to include the contents of an entire table column within the SQL code in Power Query? Background I created a spreadsheet to maintain an inventory of devices by serial number (sn)....
Hi Michael,
You may generate you parameter to filter sn:s by Power Query (let say "Filter") like
let
Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
ToText = Table.TransformColumnTypes(Source,{{"sn", type text}}),
AddString = Table.AddColumn(ToText, "Custom", each "sn = " & "'" & [sn] & "' OR "),
RemoveSN = Table.SelectColumns(AddString,{"Custom"}),
Custom = Text.Combine(RemoveSN[Custom], ""),
Parameter = Text.Start(Custom,Text.Length(Custom)-4)
in
Parameter
if serial numbers you'd like to select are in Table1. Text string with your filter is the result of it
pFilterSN = Filter
and combine with your SQL query. The only point you shall use Ignore privacy setting.
Sergei,
That's brilliant! I didn't think about creating a connection that contains the string I need. That's going to work great!
The last part I need is the syntax for including Query1 within my custom SQL statement as part of my WHERE parameter. How do I reference that?
Hi Michael,
You may use parameter in sql-native way if use Value.NativeQuery like
let Parameter = [pFilterSN = Filter], Source = Sql.Database("MyServer", "MyDatabase"), Query = Value.NativeQuery(Source, /* SQL starts-----------*/ " declare @myAnotherParameter bit = 0; --here could be any static parameter(s) SELECT * FROM MyTable WHERE @MyAnotherParameter = 0 AND SN = @pFilterSN ORDER BY SN " /* SQL ends--*/, Parameter), AnotherSort = Table.Sort(Query,{{"SN", Order.Ascending}}), in AnotherSortor combine query string with your parameter string like
let pFilterSN = Filter, Source = Sql.Database("MyServer", "MyDatabase", [Query=" declare @myAnotherParameter bit = 0; --here could be any static parameter(s) SELECT * FROM MyTable WHERE @MyAnotherParameter = 0 AND SN = " & pFilterSN & " ORDER BY SN"]), AnotherSort = Table.Sort(Source,{{"SN", Order.Ascending}}), in AnotherSort- Hi.
I tried this but I am getting the error "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.".
The parameter I am using return a text value (a list of codes) that I use as filter with a in clause ("... aufnr in (" & p_filter & ")"
Could you please help me understand why?
