join Two excel tables to return data using Microsoft query

[GaryG9595]

Hello,
I am having issues filtering data. Is there a way I can pull data from SQL using ODBC but also limit that data to only return a join from a table or sheet I create in excel?
Basically if I pull data from ODBC and I use the query to pull all fields I need to create a table, but then I want to create a list in another spreadsheet, and use some kind of join to return another table with just those names in a particular field or column.
I have a table that I return from odbc with 18,000 names and related columns of data.... I want to create another excel sheet or table with 1,300 different names and return only those 1300 names and the related columns of data....
Any way to do that in excel...? I know I can use access or sql to do it, but I am not as familiar with that.
Oh and I'm using office 365
Thanks,
Gary

 

[bbotzong]

Are you using the Query tool? If so, build two separate queries (Data tab, Get & Transform Data Group, Get Data).

Query 1: Select From File, From Workbook and navigate and select your Excel file and OK. Select the sheet and click the dropdown next to Load at bottom and choose Load To. In the dialog box, choose Only Create a Connection and Add this data to the Data model.

Query 2: Select Get Data again, then From Other Source, then ODBC. Select your connection. If you want to only pull in certain columns, click Transform. Select a column then <ctrl>+<click> to select columns you want to return with data. Right click on the header row after you've selected all and click Remove Other Columns. Don't worry, this does NOT affect your source data. When ready, click on the Home Tab, click the down arrown next to Close and Load and select Close and Load To... Same as query 1, select Only Create a Connection and Add this data to the Data model.

Finally, you'll want to create a Merge query. Click Get Data, Combine Queries, Merge. In the top, select your Excel query and in the second, select the ODBC data file. Next, you will create the join. In the top table, click on the Primary key field used to match up the data. In the bottom table, click the corresponding field. Then choose your Join kind. You might get a "Privacy Levels" warning. Read and decide if you want to ignore. Click OK. You should see your fields from the Excel file on the left and your ODBC table name in the right column. Notice there is a split arrow next to the field name. Click it to bring in your data from the ODBC query. UIse the checkboxes to bring in appropriate fields. Since you should have chosen those when creating Query 2, it shouldn't need adjusting. Click OK. Then click CLose and Load. Le Voila... your merged data table from and Excel file and your ODBC source.

Hope this helps and I hope I didn't leave anything out....