Forum Discussion

Michael Rothstein's avatar
Michael Rothstein
Copper Contributor
Jul 03, 2018

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). 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!

BI & Data Analytics
Power Query

6 Replies

  • SergeiBaklan's avatar
    SergeiBaklan
    Diamond Contributor
    Jul 03, 2018

    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.

    • Michael Rothstein's avatar
      Michael Rothstein
      Copper Contributor
      Jul 13, 2018

      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?

      • SergeiBaklan's avatar
        SergeiBaklan
        Diamond Contributor
        Jul 14, 2018

        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

         

Resources