Create intermediate Works with: Any AI

Excel Power Query Cleaning Plan for a Messy Export

Table output Has example

• 📊 Excel

Plan the applied steps that turn a monthly export into a clean, refreshable table.

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.

I receive a messy export every month and want to clean it with Power Query so I can refresh instead of repeating manual work. Describe the file: [FILE_DESCRIPTION]. Paste the first ten rows exactly as they appear, with column headings: [SAMPLE_ROWS]. What the clean table should look like: [TARGET_LAYOUT]. Known problems: [PROBLEMS].

Give the plan as a table (Step, Action, Columns, Reason, Result on sample), numbered in the order Power Query would record the steps: remove top rows, promote headers, trim and clean, change types (after splitting), split columns, replace values, filter rows (before expensive steps), unpivot, merge columns, remove duplicates, add custom columns. Where a formula is needed, write it in M style marked [VERIFY], since I will test it on my data. Show the first five result rows on my sample so I can check I understand each step, and use clear step names because they appear in the applied steps list.

Explain how to load to a table on a new sheet, how to refresh next month, and which steps would break if next month's file has an extra column or a different order, with ways to make them more tolerant. Test: row counts before and after, totals matching the source, no errors on load, dates read correctly, and no text-formatted numbers. Work on a duplicate query while testing. Menu names differ between Excel versions, so tell me what to look for.

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.

Invented steps. 1. Remove top 3 rows (report title). 2. Promote first row to headers. 3. Trim and clean all text columns. 4. Split "Name" at the comma into Surname and First name. 5. Change types: Date to Date, Amount to Decimal. 6. Filter out rows where Amount is null. 7. Remove duplicates on Invoice number.
Row check: 1,250 source rows minus 3 title rows minus 14 blanks minus 6 duplicates = 1,227 loaded. Next month's file: if the export arrives with an extra column, step 4 may fail, so select columns by name. Row check after refresh: the loaded count should equal source rows minus title rows, blanks and duplicates; a difference means a step is removing rows it should not. Keep a small table on the sheet recording the source count and loaded count for each month.

• excel