Excel create multiple MS Queries using one data connection between two sheets in the same workbook

Question

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.

Answer 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

Answer 2

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

Internally I would think you could use a pivot-table and/or a slicer.

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:

• Power Query Help
• Data Connections
• Queries
• External Links

Answer 3

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