Why to create a query just for the database connection in Power Query

Connection-only query
 

I just had an idea that I suspect will become my standard approach in Power Query, although I have not yet put into practice on a project.

Create a query that connects to your source database and only navigates as far as the list of tables in it. (You can disable the query, at this point.) Then, for any table you need from that database, reference the connection query and drill into the table you want.

Right-click the disabled connection query, Reference, then click the Table hyperlink in the Data column to query that table

There are three advantages to this I can think of:

  1. When you want to grab another table from source, it's faster/easier to reference the connection query and drill into it than to go through the "New query" menu.
  2. If you need to change the connection information at some point, it is centralized in the connection query instead of being duplicated in every query from that source. (You can still use parameters in the connection query for maintainability, if that makes sense.)
  3. The connection query provides a quick reference at any time of what tables and views are available in the database. You can even click the cell in the Data column to generate a quick preview of a table at the bottom of the screen.

Select the Table cell (don't click the hyperlink) to preview the table at the bottom of the Power Query window

That's it! Hopefully it makes us a little more productive.

Contact Form

Name

Email *

Message *