Jul 02 2018
05:01 PM
- last edited on
Jul 12 2019
11:10 AM
by
TechCommunityAP
Jul 02 2018
05:01 PM
- last edited on
Jul 12 2019
11:10 AM
by
TechCommunityAP
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!
Jul 03 2018 06:09 AM
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.
Jul 13 2018 11:49 AM - edited Jul 13 2018 11:56 AM
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?
Jul 14 2018 02:11 AM
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
May 20 2019 02:50 PM
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
May 29 2020 02:58 AM
May 29 2020 03:27 AM
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