The prompt
Score 10 of 10, Excellent
Use it when You clean the same export by hand every week or month, and want Power Query to repeat the steps each time you press Refresh.
- {{app}}
- {{sample_rows}}
- {{manual_steps}}
- {{file_arrival}}
- {{output_spec}}
Full prompt and why it works
Prompt
Turn the cleanup I do by hand on this file into Power Query steps for {{app}}, so that next time I can drop in the new file, press Refresh and get the same clean table without repeating the work. A sample of the raw data, including the header and some messy rows: """ {{sample_rows}} """ What I do by hand now, in order: """ {{manual_steps}} """ How the new file arrives each time: {{file_arrival}} What the clean table must look like (columns, order, data types, where it loads): {{output_spec}} Give me: 1. The steps in the Power Query Editor, numbered, each with the menu path (for example Home > Remove Rows > Remove Top Rows) and a step name to rename it to, such as "Removed report header". One step per manual action, merged where a single Power Query step does two of them. 2. The complete M code for the Advanced Editor in a code block, with a // comment above each step. 3. What could break on refresh, and how the steps avoid it. 4. How to check the result: the row count and one column total to compare with a file I cleaned by hand. Make the query refresh-safe: - Refer to columns by name, never by position, and remove junk rows by a rule (for example rows where the first column is blank) rather than by a fixed count, unless the count never changes. - Replace the automatic "Changed Type" step with one explicit type step for the columns I need, and set dates using the locale of the source, because the automatic step lists every column and fails when one is renamed. - If new month or category columns appear over time, use Unpivot Other Columns so they are picked up. - Put the file path or folder in a parameter instead of typing it into the query. Do not add cleanup I did not list unless refresh needs it, and then say why in that step's comment. Do not fill blanks or delete rows that my steps do not mention. A good query is one that still runs unchanged when next month's file has more rows or a slightly different title row. If a step depends on my judgment, for example "delete the bad rows", ask what rule defines them and leave a commented placeholder step "// [CHECK: rule for bad rows]" in the M code instead of guessing. If the sample does not match my description of the file, say what differs before writing the query.
Variables
- {{app}}
- Where the query will run, for example "Excel for Microsoft 365" or "Power BI Desktop".
- {{sample_rows}}
- The header and 15 to 30 rows of the raw file, including the messy ones. Mask any personal data.
- {{manual_steps}}
- Each thing you do by hand, in order, for example "delete the first 4 rows, remove the totals row, split Name into two columns".
- {{file_arrival}}
- For example "same file name, overwritten each Monday", "a new file in one folder each month" or "an export with the date in its name".
- {{output_spec}}
- Columns, their order and types, and where the result goes, for example "a table on a sheet called Clean".
Why it works
- What good looks like. Columns are found by name, junk rows by a rule and types set once, the habits that keep a query working when next month's file arrives slightly different.
- If unsure. A step that rests on your judgment becomes a question and a commented placeholder in the M code, not a rule the model made up.
- Format. Editor steps and M code side by side, so you can build it by clicking or by pasting and check one against the other.