Construct SQL where clause to pull data within Power Query

Question

Basically I want to retrieve rows of data that meet my clause conditions using Power Query.

I got 400 rows of lookup values in my spreadsheet. Each row represent 1 lookup code for example, code AAA1, AAB2 and so on

So lets say I have a select statement and I want to construct the where clauses using the above codes so my end sql statement will look like

select * from MyTable where Conditions in ('AA1', 'AAB2')

so so far I have this

let
Source = Excel.CurrentWorkbook(){[Name="Table5"]}[Content],
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Form ID",    
Int64.Type}}),
test = Sql.Database("myserver", "myDB", [Query="SELECT * FROM  myTable where" & #"Changed Type" & "])"

in test

Obviously that didnt work but thats my pseduo scenario anyway.

Please could you advice what to do? Thank you Peddie

actually instead of "where" it should be "in" but even that, it throws an error. — PeddiePooh
– PeddiePooh, Commented Mar 16, 2016 at 16:20

Mike Honey · Accepted Answer · 2016-03-16 21:24:18Z

2

I would create a "lookup" Power Query based on the Excel table. I would set the "Load To" properties to "Only Create Connection".

Then I would start the main Query by connecting to the SQL server using the Navigator to select "MyTable". Then I would add a Merge step to the main Query, to join to the "lookup" Query, matching the "Conditions" column to the "lookup" code. I would set the Join Type to "Inner". The Merge properties window will show you visually if the 2 columns you select actually contain matching data.

This approach does not require any coding, and is easier to build, extend and maintain.

answered Mar 16, 2016 at 21:24

Mike Honey

15.2k1 gold badge29 silver badges45 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Carl Walsh · Accepted Answer · 2016-03-17 07:11:43Z

Mike Honey's join is best for your problem, but here's a more general solution if you find yourself needing other logic in your where clause.

Normally Power query only generates row filters on an equality expression, but you can put any code you want in a Table.SelectRows filter, like each List.Contains({"AA1", "AAB2"}, [Conditions])

So for your table, your query would look something like:

let
    Source = Excel.CurrentWorkbook(){[Name="Table5"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Form ID", Int64.Type}}),
    test = Sql.Database("myserver", "myDB"),
    yourTable = test{[Name="myTable"]}[Data],
    filtered = Table.SelectRows(yourTable, each List.Contains(#"Changed Type"[Form ID], [Conditions]))
in
    filtered

The main downside to using the library functions is that Table.SelectRows only knows how to generate SQL where clauses for specific expression patterns, so the row filter probably runs on your machine after downloading the whole table, instead of having the Sql Server run the filter.

Collectives™ on Stack Overflow

Construct SQL where clause to pull data within Power Query

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related