1

In Excel 2021, what exactly is a "data connection", "query" and "domain source name"?

Let's say I have a Workbook "Manahil_Customer_Database.xlsm" in which I have a sheet "sht_Customer_Cities" that has a table "tbl_Customer_Cities". In a new sheet "sht_Report" I want to run two queries using one connection via MS Query. Now when I go through the MS Query route I get one Domain Name Source File "Manahil_Customer_Database.dsn" and one MS Query file "Customer_Countries_Cities.dqy" and one Connection file "Customer_Countries_Cities.odc".

However when I look at the "Queries & Connections" it says 0 Queries and 1 Connection named "Customer_Countries_Cities". I want to be able to establish a single Data Connection via MS Query from the "sht_Report" to the Workbook "Manahil_Customer_Database.xlsm" and than run multiple queries using the same connection.

Jawad
  • 8,352
  • 10
  • 40
  • 45

3 Answers3

1

If I understand the situation correctly, you are working internally, within a single file. Data connections, queries, and domain sources, are all used to associate externally.

Internally I would think you could use a and/or a .

If you provide additional details on what specifically you are trying to do, a better answer could be provided.

Some additional reading below may help further:

rayleone
  • 586
  • 1
  • 5
  • 15
1

Power Query replaced MS:Query from Excel 2016 onwards. The objects and panes you are describing relate to Power Query, not MS:Query.

Power Query is far more functional, reliable, flexible and performant than MS:Query.

For example depending on your exact requirement, you might create a base query that gathers all the required data, then refer to that base query in Reference queries that filter the output needed for each destination table.

Here's a starting point for Power Query:

https://support.microsoft.com/en-us/office/about-power-query-in-excel-7104fbee-9e62-4cb9-a02e-5bfb1a6c536a

Mike Honey
  • 14,523
  • 1
  • 24
  • 40
  • Power Query is "M" Based and MS Query is "SQL" Based. I know Power Query and BI and PowerPovit and all that. Is it not future proof to based my thing on SQL rather than some thing new that may be not in the future? Anybody remember ForPro? Guess I am old school. In any case I did as you suggested but still I get 0 Connections and 2 queries and an Connection File Manahil_Customer_Database.odc in which case the connection also saves the queries and there is no way of saving the queries alone or the connection file alone – Jawad Dec 21 '21 at 14:43
  • @jawad connections are to external sources. You would connect to another file, not a different sheet in the same workbook file. – rayleone Dec 22 '21 at 22:11
  • @jawad before Power Query I used to work with SQL every day. Since it arrived, not much. In the last 6 years, I've very rarely needed SQL at all. The PQ editor generates SQL if you really need it, but SQL coding knowledge has diminishing value IMO. – Mike Honey Dec 23 '21 at 08:25
  • @konalion ok I linked the data connection to external source. Still I cannot save the connection file and the query file as separate. It is saved as and ODC file which is exported as a connection file containing also the query. I created a connection only query as base query which is connection only and than a sub query based on the base query – Jawad Dec 24 '21 at 14:54
  • @Jawad sorry I'm not following why it matters that the connection and query are saved separately. The query is dependent on the connection, and if you want to run the same query on another data source, wouldn't you just save as a copy and connect to a different data source? – rayleone Dec 25 '21 at 16:27
1

Power Query is a MS tool that assists you on your ETL tasks. As read in a previous answer, it is based on M language.

To be able to import / modify / connect your data, the command is: DATA / GET DATA and select your input

Check this link for a quick introduction: https://learn.microsoft.com/en-us/power-query/power-query-what-is-power-query

jm_her
  • 28
  • 1
  • 6