Create dynamic Power Query SQL by using Excel table column?

Copper Contributor

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). The inventory is in an Excel table.

 

Separately, I use Power Query to connect to an external DB (Redshift) in order to return data only about the sn items I supply. A simplified version of the SQL I use is:

 

select sn, data2 from table1
where
date_key BETWEEN '2018-01-01' AND GETDATE()
and
(
sn = 'AA001' OR
sn = 'AA006' OR
sn = 'AA012' OR
sn = 'AA025'
)

 

When the list of sn's change, I need to paste the new list into the SQL.  To simplify things (because my list is hundreds of items long) I created a second table using Power Query to build the list of the exact sn's I need based on various filters. Once the table is built by Power Query in Excel, I then use the following formula to add the proper syntax to the list of serial numbers, which also excludes the last "OR" statement:

 

="sn = '"&[sn]&IF(INDIRECT("A" & ROW() + 1)="","'","' OR")

 

and creates the "Query" column as shown below based on the input from "sn"

 

sn             Query
AA001      sn = 'AA001' OR
AA006       sn = 'AA006' OR
AA012       sn = 'AA012' OR
AA025       sn = 'AA025'

 

I can now cut/paste the "Query" column into Power Query to update my list of serial numbers. While I've automated this quite a lot, there is one last thing I'd like it to do:

 

Question

Can I somehow reference the "Query" column in my SQL statement so that I no longer need to directly update the SQL statement as new devices are added or removed from the device inventory? Since the Query column contains everything I need for the data between the parenthesis in Power Query, referencing it somehow should allow the SQL to become dynamic. Is this possible? Or is there an even better way to do what I'm doing?

 

Thanks!

6 Replies

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
    AnotherSort

or 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
      

 

@Sergei Baklan 

@Michael Rothstein 

 

Hi Sergei and Michael, I know this was posted a while but I am hopeful you two can help.

 

I am trying to do this using the Value.NativeQuery route and am receiving the following conversion error.

 

DataSource.Error: Microsoft SQL: Conversion failed when converting the nvarchar value '('MyDataStringHere)' to data type int.
Details:
DataSourceKind=SQL
DataSourcePath=sysqlwh1;PPI
Message=Conversion failed when converting the nvarchar value '('MyDataStringHere')' to data type int.
Number=245
Class=16

 

The column I am using the parameter against is data type: int. I am not sure if this is the cause of the error. 

 

My WHERE clause within the NativeQuery looks like this:

WHERE (T3.INTCOLUMN_1 = 1) AND ( T3.INTCOLUMN_2 IN (@Parameter) )

 

When I manually run this query in SSMS with my copied parameter I have no issues with the data type as either an int value or a text value in the WHERE clause.

 

Do you know how to resolve this error?

 

Thanks in advance!

-Jonathan

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?

@leandrofra 

Formula.Firewall it shall be bit another story. You may avoid it by 1) "ignore privacy" settings or 2) rebuild your queries. Simplest way, if query is using as parameter or like in another query, combine them in one. Like

let
  SourceParameter = ... 
  // here are parameter query steps

  Source = ... // continue with main query
  // use SourceParameter somewhere inside

in Result