Course: Advanced Excel
Topic: Refreshing Queries to Automate Data Preparation
Course Link: Advanced Excel Course
Demo Link: Register for a Course Demo
Website: JustAcademy
In business reporting, data is frequently updated. Sales transactions arrive daily, inventory records change, customer information is modified, and financial reports are generated every month. Repeating the same cleaning and preparation steps manually can consume time and introduce errors.
Microsoft Excel provides Power Query, a data connection and transformation tool that helps users import, clean, combine, and organize data from different sources. Once a query has been created, its transformation steps can be applied again when the source data changes by refreshing the query.
Refreshing a query means updating its results using the configured data source and transformation steps. Instead of manually copying, cleaning, and rearranging every new dataset, users can refresh an existing query and reuse the established preparation workflow.
Power Query is a tool available in modern Excel versions that connects to data sources and provides a structured environment for transforming data before loading it into a worksheet or the Excel Data Model.
It records transformation steps such as removing blank rows, changing column names, filtering records, splitting columns, merging tables, replacing values, and setting data types. These steps can be applied again when the query is refreshed.
Suppose a sales team receives a new CSV file every day. Each file contains Order ID, Order Date, Product, Region, and Sales. A Power Query workflow can import the data, standardize column names, remove blank records, convert dates, and set Sales to a numeric data type.
When the source data is updated, the query can be refreshed to apply the same transformation steps to the available records, provided the source structure remains compatible.
A query is a saved set of instructions that connects to a source, retrieves data, and applies transformation steps. The query stores the preparation process rather than requiring users to repeat each action manually.
| Term | Meaning | Example |
|---|---|---|
| Source | The file, folder, database, or other location containing the original data. | Monthly_Sales.csv |
| Query | A saved connection and set of data preparation instructions. | Sales_Cleaning_Query |
| Transformation | An operation applied to the source data. | Remove blank rows or change a column type. |
| Refresh | Runs the query again to retrieve and transform data from its source. | Update the report after a new sales file arrives. |
| Load Destination | The location where the query results are made available. | Worksheet table or Data Model. |
| Refresh All | Requests a refresh of supported workbook connections and queries. | Update multiple connected datasets. |
Refreshing queries improves efficiency by reducing repeated manual work and keeping reports aligned with their configured data sources.
Refreshing does not guarantee that all source data is correct. Users should still validate the results, investigate errors, and confirm that the transformations match the business requirements.
Before a query can be refreshed, it must be created and configured. The following example demonstrates how to import and prepare sales data.
Instead of using a generic name such as Query1, use a descriptive name like Sales_Data_Cleaned. Meaningful query names make workbooks easier to maintain when multiple data sources are involved.
When you refresh a query, Excel attempts to retrieve data from its configured source and execute the saved transformation steps. The results are then updated in the configured destination, subject to the query type, connection settings, and successful completion of the refresh.
If the source file has been moved, renamed, deleted, or changed unexpectedly, the refresh may fail or produce different results. A reliable workflow includes checking the refresh status and verifying important totals.
Refreshing a single query is useful when you want to update one dataset without intentionally refreshing every connection in the workbook.
For a query loaded to a worksheet table, you may be able to right-click the table and choose Refresh. The exact available options depend on the Excel version and connection type.
Refresh All is useful when a workbook contains multiple connected datasets. It requests an update of supported connections and queries rather than requiring users to refresh each one individually.
A business reporting workbook contains three queries:
| Query Name | Source | Purpose |
|---|---|---|
| Sales_Data | Sales CSV files | Sales analysis. |
| Customer_Data | Customer workbook | Customer details. |
| Product_Data | Product master workbook | Product descriptions and categories. |
Refresh All can request updates to these connected sources. If a source is unavailable or its structure changes, the relevant query may fail even if other queries refresh successfully.
Power Query's Applied Steps list records the operations used to prepare a dataset. These steps are executed again during refresh, helping users automate repeatable cleaning tasks.
Once configured, these steps can be reapplied when the query refreshes. However, if a source column is renamed or removed, a step that depends on it may fail and require modification.
Folder-based queries are particularly useful when the same type of file is received regularly. For example, a company might store daily transaction exports or monthly sales reports in a dedicated folder.
MonthlySales/
Sales_January.csv
Sales_February.csv
Sales_March.csvIf the files have compatible structures, Power Query can combine their records into a consolidated dataset.
When a new compatible file is placed in the configured folder, refreshing the query can include its records, provided that the query's folder path, filters, and transformation steps allow the file to be processed.
Excel may provide connection properties that control when and how certain queries refresh. Available options depend on the Excel version, source connector, and type of connection.
| Setting | Purpose | Important Consideration |
|---|---|---|
| Refresh on Open | Requests a refresh when the workbook opens. | The source must be available and accessible. |
| Refresh Every N Minutes | Refreshes supported connections at a configured interval. | May not be available for every query type and can affect performance. |
| Background Refresh | Allows supported connections to refresh while you continue working. | Results may not be ready immediately. |
| Refresh All | Requests refreshes for supported workbook connections and queries. | Review completion status and dependent reporting outputs. |
| Connection Properties | Provides settings for a supported data connection. | Options vary by connection and Excel version. |
Do not assume that every Power Query connection supports every refresh option. Confirm which settings are available in your Excel installation.
| Refresh Approach | Description | Suitable Scenario |
|---|---|---|
| Manual Refresh | The user initiates a refresh. | Occasional reports or controlled updates. |
| Refresh on Open | A supported connection is configured to refresh when the workbook opens. | Workbooks that should attempt to retrieve current data on opening. |
| Interval-Based Refresh | A supported connection refreshes at a configured interval. | Use cases where periodic updates are appropriate and supported. |
| External Scheduled Automation | An approved automation workflow runs a supported refresh process. | Operational reporting that must update without a user opening Excel manually. |
Excel's built-in refresh settings do not automatically provide unattended scheduling for every workbook and connector. A process that must run reliably without a user present may require a supported service or automation platform, suitable licensing, credentials, and appropriate permissions.
Power Query can prepare data for PivotTables and charts. When source data changes, the query may need to be refreshed first, followed by any dependent summaries that do not update automatically in the same workflow.
For a PivotTable based on a query output, refreshing the query and refreshing the PivotTable are distinct operations. Depending on the workbook configuration, Refresh All may handle supported dependent connections, but the final report should still be checked.
A sales report initially contains transactions from January to March. In April, the source folder receives a new April file. After refreshing the folder query, verify that April transactions are present, refresh the relevant PivotTable, and confirm that the chart and total sales figures reflect the new period.
Refreshing works most reliably when the source structure remains consistent. Changes to column names, data types, sheet names, table names, or file locations may affect the query.
| Source Change | Possible Effect | Recommended Action |
|---|---|---|
| A new row is added. | The new record may appear after refresh. | Refresh and verify the row count. |
| A new compatible file is added to a folder. | The query may include the file if it passes the query's filters. | Refresh and verify the new records. |
| A column is renamed. | Later transformation steps may fail. | Update the affected query steps. |
| A required column is deleted. | Formulas or transformation steps may fail. | Restore the column or redesign the transformation. |
| A source file is moved. | The query may not find the file. | Update the source path or restore the file location. |
| A number column contains text. | Data type conversion errors may occur. | Investigate the unexpected values and define a suitable conversion rule. |
| Column headers become inconsistent. | Combined files may create extra columns or missing values. | Standardize headers or add a transformation to handle variations. |
Refresh errors can occur because of unavailable files, changed source structures, authentication issues, invalid values, or network problems. A successful workflow includes identifying the cause rather than repeatedly refreshing without investigation.
| Error | Likely Cause | Solution |
|---|---|---|
| File not found | The source file was renamed, deleted, or moved. | Check and update the source path. |
| Access denied | The user lacks permission to access the source. | Confirm file permissions and approved credentials. |
| Authentication failure | Credentials have expired or changed. | Review the connection's authentication settings. |
| Column not found | A required source column was renamed or removed. | Review the source structure and update dependent steps. |
| Data type conversion error | A column contains values that cannot be converted to the expected type. | Inspect error rows and correct the data or conversion logic. |
| Query takes too long | Large files, complex transformations, or slow source connections. | Reduce unnecessary columns and rows early and review the query design. |
| New files are not included | Folder filters or filename rules exclude the files. | Review the folder query's filters and source location. |
| Report totals do not change | The source did not change, the refresh failed, or the report has not been refreshed. | Check the query output and refresh dependent PivotTables or calculations as required. |
Scenario: A company receives a separate CSV file for each month's sales. The files contain Order ID, Order Date, Product, Region, and Sales. The reporting team currently combines the files manually and repeats the same cleaning steps every month.
The workbook should contain a repeatable query that combines the selected monthly files, applies the saved preparation steps, and supports updated reporting after a successful refresh.
Project Objective: Build an Excel dashboard that uses Power Query to automate the preparation of recurring business data.
Answer: Power Query is a data connection and transformation tool that imports data from supported sources and helps users clean, combine, and prepare it for analysis.
Answer: Refreshing a query reruns the configured data retrieval and transformation process to update its results from the source.
Answer: Refresh updates a selected query or connection, while Refresh All requests updates for supported connections and queries in the workbook.
Answer: It stores transformation steps such as filtering, replacing values, changing data types, and combining files. Those steps can be reapplied during refresh.
Answer: Yes, a correctly configured folder query can include new compatible files when refreshed, provided they are not excluded by filters and follow the expected structure.
Answer: It is a setting available for supported connections that requests a refresh when the workbook opens.
Answer: A transformation step may refer to the original column name. The affected step must be reviewed and updated to match the new structure.
Answer: Not necessarily. Query refresh and PivotTable refresh can be separate operations, depending on the workbook configuration. Verify that dependent reports reflect the updated data.
Answer: Remove unnecessary columns and rows early, simplify transformations, avoid redundant steps, and use an appropriate load destination.
Answer: Validation confirms that the query loaded the expected records and that important totals, dates, and fields are correct.
Answer: Common causes include missing files, changed source structures, invalid data types, unavailable network locations, and authentication or permission problems.
Answer: No. Built-in refresh options vary by connection type and Excel version. Unattended automation may require an additional supported service or automation platform.
Refreshing queries is an important Advanced Excel skill because it allows users to repeat data preparation without manually performing every cleaning and combining operation. Power Query can retrieve data from supported sources, apply saved transformation steps, and load the results for analysis.
By creating clear queries, keeping source structures consistent, configuring appropriate refresh settings, and validating results, you can develop reliable workflows for recurring reports and dashboards. Refreshing should always be combined with sensible error handling and data-quality checks.
To build more practical spreadsheet and reporting skills, explore the Advanced Excel Course at JustAcademy, visit the JustAcademy website, or register for a course demo.
