Prompt Almanac

Create intermediate Works with: Any AI

Power Query: Combine Many Excel Files From a Folder

Checklist

Combine many Excel or CSV files from one folder with Power Query, including step-by-step setup, cleaning steps and a refresh routine.

Open in ChatGPTOpen in Claude

The Open buttons send the prompt text as currently shown below to that site only when you click. Fill in the placeholders first.

[VERIFY] is an editorial flag, not a blank to fill. Wherever the prompt shows [VERIFY], check that item against a reliable source before you use it.

Rate this prompt

Loading ratings…

Guide me through using Power Query to combine many files from one folder into a single table. Folder contents: [FILE_TYPE] files, about [FILE_COUNT] files, named like [FILE_NAME_PATTERN]. Each file has these columns: [COLUMN_LIST]. Differences between files that I know about (extra columns, different header row, sheet names): [FILE_DIFFERENCES]. My Excel version and platform: [EXCEL_VERSION] ([VERIFY] menu paths).

Give numbered steps with exact menu paths for: connecting to the folder, choosing Combine and Transform, picking the sample file, promoting headers, changing data types, removing empty rows, adding a column that holds the source file name, filtering out junk files, and loading the result to a table.

Then explain:

- What happens when I add a new file next month and how to refresh.

- How to handle files with different column names or extra columns.

- Three common errors (type errors, "expression error", locked files) and fixes in a table with columns Error, Cause, Fix.

- A safe-practice checklist: keep raw files untouched, one folder per data set, test with two files first.

Write it for someone who has never opened the Power Query editor. Do not write VBA or M code unless a simple edit is needed; if you do, explain each line. Use my column names in the examples, and mark version-specific steps [VERIFY]. Do not assume the files are consistent; tell me how to check.

Fill in before use

Optional: type your details and the prompt above updates. Nothing is saved or sent.

Replace each bracketed placeholder with your own details, then paste into Any AI or another assistant.

Related prompts