iTechGuides is reader-supported. When you buy through links on our site, we may earn an affiliate commission. As an Amazon Associate I earn from qualifying purchases. Learn more
I stopped recreating the same Excel report each month by setting up Power Query to import and transform recurring source data, then refreshing the query when new data arrived. In my workflow, the repeat work became a refresh step—not because every workbook is automatically a one-click fit, but because the source files stayed predictable.
What Power Query does in a recurring report
Power Query, also called Get & Transform in Excel, connects to data, reshapes it, and loads the result into a worksheet or another supported destination. Once the transformations are configured, Excel can apply them again when you refresh the query. Microsoft Support describes the behavior this way: “Power Query automatically applies each transformation you created.” Microsoft’s refresh tutorial explains the process.
The useful change is that the report’s repeatable rules—such as selecting columns, filtering rows, and setting data types—live in the query rather than in a sequence of manual edits. The query output is generated from the source; it is not the place to enter next month’s raw data.
Choose the setup that matches your monthly input
One workbook or table that grows over time
If each month’s records are added to the same stable source table, connect Power Query to that table and build the required transformations once. Add the next month’s records to the original source table, then refresh the query.
#1 Best Overall
- The Microsoft Office 365 Bible: The Most Updated and Complete Guide to Excel, Word, PowerPoint, Outlook, OneNote, OneDrive, Teams, Access, and Publisher from Beginners to Advanced
- ABIS BOOK
A folder containing one file per month
If each month arrives as a separate, similarly structured file, use Excel’s folder connector: Data > Get Data > From File > From Folder. Keep only intended report inputs in a dedicated folder. Before combining, inspect the file list and exclude unrelated files or subfolders so they do not become part of the report.
Choose Combine & Transform when you need to inspect or shape the data before loading it. Excel creates supporting queries, including a Sample File query and a Transform File function, alongside the final results query. The sample transformation provides the pattern applied across the combined files, so changes to that transformation can affect how the folder’s files are processed. Microsoft’s folder-import guidance documents the setup.
Make monthly files consistent
For reliable folder combination, use the same column headers, data types, and number of columns in each file. Column order can differ because Power Query matches columns by name. Files that add, remove, or rename columns can require you to adjust the transformation before the combined output is correct.
What’s actually slowing this PC down?
Pick the symptom - the matching free tool is one click away.
Append rows; merge on a key
These operations solve different problems:
- Append stacks rows from queries. Use it when monthly extracts have the same kinds of columns and should form one longer table.
- Merge joins data by matching values in a shared column. Use it when distinct tables, such as transactions and a lookup table, should be connected through a common key.
For a folder of monthly files that should become one continuous dataset, the goal is row stacking. For separate tables that need fields matched by an identifier, use a join. Microsoft’s query-combination guide explains Append and Merge.
Build the report once, then update the source and refresh
- Prepare the input. Choose a stable Excel table or a dedicated folder of monthly files. Keep the expected headers and data types consistent.
- Connect in Excel. For a folder, use Data > Get Data > From File > From Folder, inspect the listed files, and select Combine & Transform if you need to review or shape the combined data.
- Shape the query. Set the transformations the report needs, such as selecting columns, filtering records, or assigning data types, then load the result to the intended destination.
- Add the next month’s data to the source. Put new rows in the original source worksheet or add the next file to the input folder. Do not type or paste new source data into the Power Query output worksheet.
- Refresh. Refresh the individual query or use Refresh All to update the workbook’s queries. Microsoft’s instructions for adding data likewise direct users to change the original data worksheet and refresh the query. Read the refresh steps from Microsoft Support.
Pick an output and Excel environment that will refresh reliably
A query can load its result to a worksheet table or use another supported destination, such as a Data Model or a connection-only query. Select a destination based on how the report will be used, then confirm that the Excel version and source support the refresh workflow you need. Power Query is available in Excel for Windows, Mac, and the web, but capabilities differ by platform. Microsoft’s overview of Power Query in Excel describes its role and availability.
In Excel for the web, Microsoft documents Refresh All and individual query refresh for supported sources. Viewing and refreshing queries are available to Microsoft 365 subscribers, while some additional functionality requires business or enterprise plans. The web version cannot refresh queries loaded to the Data Model, workbooks saved in a third-party cloud location, or sources that require an on-premises data gateway. Microsoft also documents a limit of 1,000 refresh connections per user. Check the current source, account, and workbook setup before relying on web refresh. See Microsoft’s Excel for the web guidance and its version and data-source compatibility information.
Rank #4
Excel for Mac has its own source and refresh guidance. For file-based sources, the first refresh may require updating the file path. The Mac refresh guidance does not establish that every Windows authoring feature is available on Mac, so confirm that the features needed to build or edit the query are supported in your environment. Microsoft’s Excel for Mac instructions cover its documented workflow.
What makes the “one refresh button” approach work
The refresh is only the last step of a repeatable pipeline. It works when the source remains accessible and the incoming data still fits the structure the query expects. A stable input location, consistent headers and types, the correct combination operation, and a supported refresh environment are what make the monthly update predictable.
For query-specific controls and troubleshooting, consult Microsoft’s guide to managing Power Query queries and the Power Query for Excel help page.
Quick Recap
Product prices and availability are accurate as of the date/time indicated and are subject to change. Any price and availability information displayed on Amazon at the time of purchase will apply.
