# Combine monthly files with Power Query, using an LLM one step at a time

> Point Power Query at a folder of same-shaped files, then use ChatGPT or Copilot only to write or explain individual M steps, testing each one before adding the next.

- **Category:** [Excel](https://stepgleam.com/categories/excel)
- **Difficulty:** Intermediate
- **Time:** ~40 minutes
- **Tools:** Power Query, Excel (Microsoft 365), ChatGPT
- **Reliability score:** 82/100
- **Steps:** 6
- **Last updated:** 2026-10-26
- **Published:** 2026-10-26

## Tools used

- [Power Query](https://learn.microsoft.com/en-us/power-query/) — Combines the files and holds every transformation
- [Excel (Microsoft 365)](https://support.microsoft.com/en-us/excel) — Where the combined table lands
- [ChatGPT](https://chatgpt.com) — Writes or explains one M step at a time

## What you'll get

One refreshable query that stacks every file in a folder, with any extra M steps written and tested one at a time.

## Who this is for

Analysts who get the same export every month and paste the files together by hand.

## Before you start

An r/excel user who builds Power Query merges with ChatGPT describes a lesson others repeat: break the job into small pieces, because long requests come back wrong or cut off. Microsoft's folder-combine steps do most of the work without any AI. This guide puts the two together.

## Steps

### Step 1: Put only same-shaped files in one dedicated folder

Create a folder that holds only the files you want stacked. Microsoft's guidance is to keep them in *"a dedicated folder without extraneous files"*, each with the same column headers, data types and number of columns. Column order doesn't matter because columns are matched by name.

**Why this works:** Anything else in the folder gets pulled into the query.

[Open step 1](https://stepgleam.com/guides/combine-monthly-files-with-power-query-and-an-llm#step-1)

### Step 2: Connect with Data → Get Data → From File → From Folder

**Tool:** Power Query

Select the folder, then choose **Transform Data** so the Power Query Editor opens with the list of files. Filter the **Extension** or **Folder Path** column so only the files you want remain.

[Open step 2](https://stepgleam.com/guides/combine-monthly-files-with-power-query-and-an-llm#step-2)

### Step 3: Combine Files from the Content column

**Tool:** Power Query

Select the **Content** column, then **Home → Combine Files**. Power Query uses the first file as the example; pick a different sample file from the drop-down if the first one is unusual. Only tick **Skip files with errors** if you will check which files were skipped.

**Why this works:** Power Query builds the helper queries for you; no M needed yet.

[Open step 3](https://stepgleam.com/guides/combine-monthly-files-with-power-query-and-an-llm#step-3)

### Step 4: Ask the LLM for one transformation at a time

**Tool:** ChatGPT

For anything the ribbon can't do easily, describe one change and paste the current query from **Advanced Editor**. The r/excel user who works this way: *"it's best to break the project into small pieces"* because *"the more complicated my instructions are, the less likely ChatGPT 4 is to get them right. Therefore, I break down the project into steps and test each step along the way."*

**Why this works:** Short asks are easier for the model to get right and for you to check.

**Prompt:**

```
Here is my current Power Query M (pasted below). Add one step after #"Changed Type" that removes rows where [Amount] is null. Return the full query and explain the new step in one sentence.
```

[Open step 4](https://stepgleam.com/guides/combine-monthly-files-with-power-query-and-an-llm#step-4)

### Step 5: Check the new step in Applied Steps before moving on

**Tool:** Power Query

Paste the result back into Advanced Editor, click the new step in **Applied Steps**, and check the preview's row count and columns. Another r/excel user who has Copilot define M steps warns: *"It makes mistakes, though, so you have to check it and fill the gaps anyway."*

[Open step 5](https://stepgleam.com/guides/combine-monthly-files-with-power-query-and-an-llm#step-5)

### Step 6: Close & Load, then refresh when new files arrive

**Tool:** Excel (Microsoft 365)

Select **Close & Load**. Next month, drop the new file into the same folder and refresh; Microsoft's walkthrough describes setting it up once and then refreshing to see each month's results.

[Open step 6](https://stepgleam.com/guides/combine-monthly-files-with-power-query-and-an-llm#step-6)

## Pro tips

- Keep a copy of the working M in a text file before you paste in an AI edit.
- If one file has an extra column, fix the file rather than adding special-case M.

## Common mistakes

- Pasting a long query and asking for five changes at once.
- Leaving unrelated files in the source folder.
- Ticking Skip files with errors and never checking what was skipped.

## FAQ

### Do I need AI for this at all?

No. The folder combine is point-and-click. The users quoted here use AI only for extra M steps they can't build from the ribbon.

### Copilot or ChatGPT?

The sources use both. Either works for one-step-at-a-time help; check every step either way.

---

Canonical guide: https://stepgleam.com/guides/combine-monthly-files-with-power-query-and-an-llm
Read this guide as Markdown: https://stepgleam.com/guides/combine-monthly-files-with-power-query-and-an-llm.md