Power query

Hi Anna,

Thank you for the forum question.

Yes, you can absolutely use Power Query in Excel to collate data from a daily Excel report that is dropped into a folder. This is a common and powerful use case for Power Query, often referred to as "Folder Query" or "Combine Files from Folder".

Here’s a step-by-step guide to set it up:


Step-by-Step: Combine Daily Excel Reports from a Folder

1. Organize Your Folder
- Place all your daily Excel reports into a single folder.
- Ensure the files have a consistent structure (e.g., same sheet names and column headers).

2. Open Excel and Launch Power Query
- Go to the Data tab.
- Click Get Data → From File → From Folder.

3. Select the Folder
- Browse to the folder where your daily reports are stored.
- Click OK.

4. Combine Files
- Power Query will show a list of files in the folder.
- Click Combine → Combine & Transform Data.
- Power Query will open a preview of one of the files and ask you to confirm how to extract the data (e.g., from a specific sheet or range).

5. Transform the Data (Optional)
- You can now clean, filter, or reshape the data as needed using Power Query’s tools.
- For example, remove unnecessary columns, filter rows, or add calculated columns.

6. Load the Data
- Click Close & Load to load the combined data into Excel.


Automating the Process

Once set up, you can simply drop new files into the folder, and then:
- Open your Excel file.
- Click Refresh All on the Data tab to update the combined data with the new files.



Kind regards

Jens Bonde
Microsoft Office Specialist Trainer

Tel: 0207 987 3777
STL - https://www.stl-training.co.uk
98%+ recommend us

London's leader with UK wide delivery in Microsoft Office training and management training to global brands, FTSE 100, SME's and the public sector