Referencing Multiple Queries

In Power Query Editor, you can create a reference query that aggregates data from multiple different queries by using the "Append Queries" feature, and this can be set up to automatically refresh when the source queries are updated. Here's a step-by-step guide on how to achieve this:

1. Open Power Query Editor:

   • In Excel: Go to the "Data" tab, click "Get Data," and select "Launch Power Query."
   • In Power BI: Go to the "Home" tab and click "Edit Queries."

2. Create your individual queries for each data source that you want to combine. Ensure that these queries are properly set up to retrieve and transform your data.

3. To aggregate these queries into a single one, go to the "Home" tab in Power Query Editor.

4. Click on "Combine Queries" and select "Append."

5. In the "Append Queries" dialog, you can add the queries you want to combine. Select the queries you want to aggregate, and click "OK." You can select multiple queries by holding down the Ctrl key (Cmd key on Mac) while clicking on them.

6. Power Query will create a new query that appends the rows from the selected queries into a single query. This new query can serve as your aggregated query.

7. To ensure that your aggregated query automatically updates when the source queries change, you need to set the refresh options correctly. In Power Query Editor, go to "File" and select "Options and settings," then choose "Options."

8. In the "Options" dialog, navigate to the "Query Options" section. Make sure that "Enable Load" is checked for the aggregated query, and that you set the refresh options for each of the source queries accordingly.

By following these steps, your aggregated query will automatically update when you refresh your data in Power Query. This way, any changes made to the source data in your individual queries will be reflected in the aggregated query. This approach is more efficient than manually updating an aggregated query whenever the source data changes.