Referencing Multiple Queries

[ChrisR22]

Hello,

In Power Query Editor I am looking to create a reference query that takes reference data from multiple different queries. Essentially I have 3+ queries that I will be updating regularly, and I would like those updates to flow into an aggregated query that has all of those rows appended. Because the source data for each of these queries will be updating regularly, a simple append will not reflect the frequent updates. 

Has anyone found a solution that would allow (or replicate) a multiple query reference into a single aggregate query?

Alternative workarounds would be welcome, thanks!

[ChrisR22]

Hello @123abc,

Thank you for the response. In following your instructions, I reached step 8, however I do not see "Query Options" or "Enable Load" in this Options dialog box. 

Is this the enable load you were referring to?

This is for loading into power BI from the query editor, I don't believe this is what you meant but it is the only instance of "Enable Load" I have seen in PQE.

[123abc]

I apologize for the confusion. It seems there was a misunderstanding. The "Enable Load" option is not relevant to Power Query Editor in Excel. Instead, you should configure data refresh settings in Power Query using the Power Pivot or Power BI tools. In Power Query Editor within Excel, there isn't a direct option to schedule data refresh.

To achieve automatic data refresh in Excel, you'll need to use the Power Pivot and Power Query features within Excel or consider using Power BI Desktop for more advanced data modeling and transformation. Here's a high-level overview of the process using Power Pivot in Excel:

1. Load your data into the Power Pivot model: In Power Query Editor, load your data into the Power Pivot model by selecting "Close & Load To" and choosing "Add this data to the Data Model."

2. Create relationships: If your data sources have common fields, you can establish relationships between tables in the Power Pivot model.

3. Configure data refresh: In Excel, go to the "Data" tab and click on "Queries & Connections." Then, right-click on your data source and select "Properties." In the "Connection Properties" window, you can configure the data refresh settings by clicking the "Usage" tab.

4. Schedule data refresh: If you want to schedule data refresh, you will typically need to use the Power BI Gateway (if your data is not local) and Power BI Services (if you want to refresh data in a shared environment).

The exact steps for configuring data refresh may vary depending on your specific version of Excel and the version of Power Pivot available to you. It's essential to consult the documentation and resources specific to your environment and Excel version for detailed instructions.

If you're working with Power BI Desktop, the process is similar, and you can schedule data refresh more easily from within the Power BI Service.

In summary, to schedule data refresh in Excel, you should leverage the Power Pivot, Power Query, and related features, and the exact steps may vary based on your Excel version and environment.

If this post helps, then please consider Accepting it as the solution to help the other members find it more quickly.

In case there is still a problem, please feel free and explain your issue in detail, It will be my pleasure to assist you in any way I can.

[ChrisR22]

@123abc is this assumption correct:

I append three queries together. The underlying data source (excel file) for any of those original queries is updated. When I click 'Refresh Preview' in Power Query Editor, these updates would flow through to not only the original query, but to the appended (aggregated) query, as well as any other query that this query was appeneded or merged with.

Is that correct?

If not, I am just looking for a way for those updates to flow through to an aggregated query, so that I don't have to create a new aggregate each time one of the queries is updated.

[v-stephen-msft · Accepted solution]

Hi @ChrisR22 ,

When your data source data is updated, your append query is also updated when refreshed, and you don't need to appear to write a new append query.

Append queries - Power Query | Microsoft Learn

Best Regards,

Stephen Tao

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.           

[123abc]

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.