PowerQuery in Excel: Use an Excel table to populate SQL table in PowerQuery

I want to use a list of Excel table values to populate a table in a SQL Query within PowerQuery in Excel.

I have a query (simplified) that looks like the following:

(see attached image. The forum won't allow me to post the code within the BBC at all (shows up as SQL code), and won't let me post it as a screenshot inline until I've had more posts.) <--mini-rant (But I do understand why.)<--mini-reconciliation to my lot in life.


Currently, I have an Excel table containing the list I need. I have brought it into PowerQuery as a table, set my field-value type to "ABC", filtered the table down to what I want, and drilled down on one column with the values I want (which turns into a list).

Now, I want to use my list to populate the section in my code above "[Need code for here]" so that the results of the SQL query will show up in Excel as the result of my MS SQL query.

This logic is basically serving as a filter list (via a join) for a larger query.

Have a read of thread below. I think this is similar to what you are trying to do?

https://www.excelforum.com/excel-pro...cel-range.html

Ok, so you have query result as list already.

In that case in your query to SQL, you can reference that query.

Let's say that your list is named "ListA".
You can use something like...

Please Login or Register  to view this content.

Wow - it's a real struggle to post here with all of the restrictions on new members and the "SQL Injection" warning. I can't post code or links. Please ignore my "..."s.

Could the issue I'm encountering now be based on the INSERT... INTO clause? I have it as INSERT... INTO... MyTable VALUES... . The Values method requires the delimiters to look like this: ('thevalue'), ('thenextvalue')

type your links out instead of copying and pasting. You should be able to post code and surround it with code tags. Look at the rules for instructions for that. As to uploading files, do not use the paperclip--it is not functional. Click on Go Advanced in the lower right hand corner of the reply window and follow the wizard instructions.

Oh, didn't realize you are trying to "INSERT" into SQL table using M query code.

DON'T

This is extremely bad idea, as you can insert undesired data into table. It isn't easy to control it.
As any change to query code will have the query sample refresh and it will insert each time.

Having said that, to construct the string, you'd use List.Transform function.
Ex:

Please Login or Register  to view this content.

EDIT: Instead, create API endpoint to perform insert operation on the server and use VBA or some other means to send info to endpoint.

It worked! Thanks so much. I'm bookmarking this page for future reference.

The application for this was inserting it into a temporary table (at)TempTable - not for a true INSERT. I've tried that before via VBA, and found that the results didn't always come through correctly on the other end. Luckily, I was testing and not working with production tables.

This Excel table is being used as a join on the main dataset being output to another worksheet. The temp table is created at the beginning of the query, and then is used as a "LEFT JOIN (at)TempTable B ON A.description like ('%' & B.ColumnHeaderName & '%')".

It creates a list of search terms that can be found within the text of a varchar-formatted field.

So, an Excel user can type a list of terms into column A of the filters table, and then refresh the query results. The results look like this:

Filter Criteria
---------------
Ball
Bat
Bas


Orig Dataset:
Baseball | Ball
Baseball | Mitt
Baseball | Bat
Baseball | Bases
Baseball | Field
Football | Football
Football | Pads
Football | Helmet
Golf | Golf Ball
Golf | Tee
Golf | Basic Golf Cart


Results after filter:
Baseball | Ball
Baseball | Bat
Baseball | Bases
Football | Football
Golf | Golf Ball
Golf | Basic Golf Cart


I checked the results, and it appears to work perfectly.

...not for a true INSERT...

Ah, that makes more sense.

You are welcome and thanks for the rep.

If you are satisfied with the solution provided. Please mark the thread as solved by using thread tools found at top of your initial post.