CODEWITHSUNDEEP
sqlexcelms-access
excelguy
excelguy

Reputation: 1624

where condition to be a specific excel sheet range

I an excel sql access query, it concatenates some fields and then narrows down the data set with a where clause. I have many where clauses, I am wondering if its possible to declare a sheet name with header/range as my where clause instead of having a list of +100 where strings.

My query is, 

select b.*
from (Select B.*, B.[RF attribute1] & "|" & B.[RF attribute2] & "|" & B.[RF attribute3] & "|" & B.[RF attribute4] as new_field 
      from [BlackMonday1987$] as B 
     ) as b
where new_field in ('a','b','c')

But how do I do something like, where this clause will use all fields within header1 on sheet 1. I have tried the below, not sure if it can be done or if its the right logic.

where new_field = [Sheet1$].[header1]

Upvotes: 0

Views: 134

Answers (2)

Roel
Roel

Reputation: 11

In case anyone is still looking:

  1. Add a table range to powerQuery editor: Data - From Table / Range
  2. Transform - Convert To LIST - Name = "Table1"
  3. New Source - From Database - From SQL - Advanced - SQL statement - Add your query
  4. Right Click the New Query - Advanded Editor
  5. Inject your list using Select * from table where VALUE in ( " & Text.Combine(List.Transform(Table1, each "'" & _ & "'"), ",") & " )

You may need to alter privacy settings to "Always ignore Privacy Level setting"

Upvotes: 1

Gustav
Gustav

Reputation: 56026

The IN clause cannot be dynamic. It must be fixed values.

You can create the full SQL from code, but that's it.

Upvotes: 0

Related Questions