Can I link to a query from another workbook?

[Last_Son]

I need to create an interim reporting solution while my engineers finish the huge task of building a data lake with our diverse data sources.

I created a report in Excel using Power Queries to transform 300k+ records into multiple tables of different formats. The power queries work well but there is enough data to make the file large and slow. My plan is to have a separate file that collects the finalized query tables without all of the background data.

I can do this with a link to the tables, but I would like to connect to the power query instead.

To illustrate:
File A...
... would contain multiple sheets with 200,000 to 300,000+ records each
... Power Query would convert the records to pivoted tables, organized by source and month

File B...
... would link to the queries in File A and display the tables
... after each refresh, we would save a copy of File B and break links to make the data static

Does anyone know if I can link to a power query from an external workbook?

 

[vletm]

Last_Son
You wrote few times about Power Query to Ask an Excel Question-forum
Power Query has own Forum >>> Moved this thread to Power BI, Power Query and Power Pivot

 

[LorettaParks]

Hi,
Yes, it is possible to link to a Power Query from an external workbook.

Here's how you can do it-

1. Open File B, the workbook where you want to display the data from the Power Query.
2. In Excel, go to the "Data" tab in the ribbon and click on "Get Data" or "From Other Sources".
3. Select "From File" or "From Workbook" and navigate to File A, the workbook containing the Power Query.
4. Choose the option to import data from the workbook. This will open the Power Query Editor window.
5. In the Power Query Editor, select the desired Power Query query that you want to link to.
6. Click on the "Home" tab in the Power Query Editor ribbon and select "Close & Load" or "Close & Load To".
7. In the "Import Data" dialog box, select the desired destination for the linked data in File B. You can choose to load the data into a new worksheet, an existing worksheet, or a data model, based on your requirements.
8. After selecting the destination, click "OK" to create the connection and load the data.

Now, the data from the Power Query in File A will be linked and displayed in File B. You can refresh the connection to update the data in File B whenever the Power Query in File A is refreshed.

Note: Both File A and File B should have the Power Query functionality available, as some older versions of Excel may not support Power Query.

Best Regards.

 

[p45cal]

You can refresh the connection to update the data in File B whenever the Power Query in File A is refreshed.

That sounds quite good! I'm guessing that File B has to be open when you do that?
@LorettaParks , now the reverse question:
before I (I'm not the OP) set everything up to test for myself, would you happen to know whether refreshing File B's query which connects to the closed File A query, also refreshes the query in File A? I would doubt it, but perhaps you know for certain?