multiple queries from same database

(I assume this is the best forum to discuss data connection issues in Excel? If a moderator thinks it belongs elsewhere, please let me know. Thanks)

I'd like to create a workbook with about 100 identically formatted tables from the same mysql database. (The mysql table has around 200 tables in it).

Each database table has identical labels.

Here's an example schema:

DB name: ospreytwice

Table name: river

Column Names:

• Datestamp
• River_Name
• River_Length

I've been playing around with the DB features all day but can't seem to find a way to configure Excel to make just one DB connection to obtain the values in these separate tables.

Must I need to create a separate query (and connection) for each table? I don't think my db server would allow that. (Also, there are lot of other tables and formulas in the db so efficiency is important).

Obviously I don't want to make the db connection info public, but if it will help you, then PM me and I can send the credentials so that you can see what I'm working with.

Thanks!

You can use PowerQuery (Get&Transform) to connect DB into your workbook then create Reference to the basic Query as many as you wish and transformation possibilities of the References are limited only by your imagination.

Thanks Sandy. When you say "Reference" are you referring to the various lookup functions? I'd need to only load the first 300 rows of each table (the db has about 37,000 rows, used for other purposes).

Also, I've recently updated to v 1711 and the data ribbon is a bit different than earlier versions. So upon reading your suggestion to use Get & Transform, I clicked and noticed this option to merge or combine queries (see image). You think that might be better?

data merge append.png

Thanks