Forum Discussion
Retrieving Data From Multiple Tables of Connected SQL Database Into One Excel Workbook
I have an Excel workbook that I was able to connect to a vendor's SQL database with hundreds of tables. Rather than bring the relevant SQL tables one by one into their own Excel sheets I'd prefer to retrieve the data from SQL already combined the way I want it. For example, this is a postage database and the SQL transaction history shows a 4 digit number for the type of postage used (1st Class, Priority, etc.) and the "type of postage" needs to be retrieved from a different SQL table with a list of various types of postage by 4 digit number. I'd prefer to bring the transaction history data into Excel with the actual type of postage, not the 4 digit code. Thanks for the help!
2 Replies
In "click-button" scenario you can connect to SQL DB via "Get & Transform" to the fact table and dimension table in two separate queries, then in the first query (fact table) apply "Combine queries" (or "Merge queries" - I can't check it right now) button (you can find it in the Main ribbon of the Query Editor windoe) and it will help you to perform left outer join by postage type code column.
Then all you need is to expand the joined table (actually you can select only one column from this table).
If you make this in the very first steps, then it can apply query folding to the source DB, so all join operations will be performed on the DB side and only then combined data will be loaded to Excel.
With the little M-coding you can make this in one query, BTW
Thanks very much- that worked very well. One more question- is there a way to restrict the ODBC connection so that our users don't accidentially send data changes BACK to the SQL database?
