Power Query Advanced SQL - 'Where' from excel range

[Steveapa]

Hi All.

Looking to keep my queries as fast as possible - and user friendly and modifiable (by the non sql user).

1) Is there a way to use a named range (from a sheet in the file) in my Power Query "From ODBC" Advance options Sql? I am querying an oracle database and by using the advanced options, SQL, my data set can be filtered at the source - Much faster. But, I would like to be able to have the "Where" statement use a range of cells. Can it be done?

I can use a named range of cells (= Excel.CurrentWorkbook(){[Name="User_ID"]}[Content]) to create a "table" to use to filter the results of #1 above, but that filters them after I have retrieve the original dataset from the oracle server. Slower...

I have as my original query / data set...

Please Login or Register  to view this content.

2) In the where statement above, I would like to be able to have the "PROGRAMS.DML_USER_ID" be a range in the local excel file. Can it be done???
3) Can I use vba to concatenate a where statement to be added to the original sql?
4) Can I have a vba function return a statement to the "where" sql for the original data set?
5) I can create a VBA DAO data set from an sql statement to oracle where I can create (in vba) the entire sql statement. Can this be the data source in power query?

Thanks.
Steve
Harrisburg, PA

[CK76]

1) Yes, you'd pass it on as variable.
2) Yes. You'd use Text.Combine() to generate concatenated list. Then use Where PROGRAMS.DML_USER_ID IN (list).
3) No need for VBA
4) No need for VBA
5) No need for VBA.
Ex: This should go after let, but before source (in advanced editor).

Please Login or Register  to view this content.

Then your where clause would change to something like... #(lf) means newline in "M" code.

Please Login or Register  to view this content.

Have a read of small article I wrote about using variables for PQ Native Query. Post #5 & #6. There are some drawback to using this method.
https://chandoo.org/forum/threads/us...s-notes.35658/

[Steveapa]

Thanks... for me to understand better... If my sql is the following... please edit it to show how it should be with a range named "USER_ID_INCLUDED". This range would hold in a column: TE377256 and row below US045888

My sql in "Advanced options"

how would I do it with myvar??

Please Login or Register  to view this content.

[CK76]

Not in "Advanced Options".

You should initially bring in using hard coded query. Then go into Query editor. Go to View Ribbon -> Advanced Editor.

There you will see something like below (this example uses accdb, but same applies to ODBC to Excel).

Please Login or Register  to view this content.

This is changed to...

Please Login or Register  to view this content.

[Steveapa]

Dude, Awesome! Now I get it! Works Great! Also, now I understand the "Advanced Editor" on the ribbon - it is the summary code for every step in the "Query settings" on the right pane's "Applied Steps".

Now the sql will run on the server and only return the results I am interested in - SPEED! - and any user can update a value in the excel tab's named range and the sql will get that data!
Advanced Editor code below...

Please Login or Register  to view this content.

Thanks Again.
Steve

[Steveapa]

Right pane view...
Attachment 625661

[CK76]

You are welcome

If this solved your query, please mark the thread as "Solved" by using Thread tool found at top of your initial post.

[joeyslaptop]

This is super close to answering my question as well. How do I do this from a list (from a table already brought into PowerQuery, filtered, and drilled down on one column to create the list)? I think I'm close, but don't know how to convert my list into the text needed in the Advanced Editor.