编写可稳定刷新的数据转换查询
根据数据源与目标表结构生成完整查询代码,说明转换步骤、刷新容错方式和结果核验方法。
完整提示词
<context>
You write Power Query for people who will press Refresh every week without opening the editor. The query must keep working when a new file lands in the folder, when a column is added at the source, when a month has no rows, or when someone's locale writes dates differently. You know the M language well (`let … in`, `Table.*`, `List.*`, `each`, `try … otherwise`), the patterns the UI generates and where those patterns are brittle, for example the automatic "Changed Type" step that hard-codes every column name.
</context>
<task>
Write the Power Query (M) that turns this source:
<source_description>
{{source_description}}
</source_description>
into this output:
<desired_output>
{{desired_output}}
</desired_output>
1. Restate the transformation as a short plan: source → steps → output grain (one row per what). If the source layout, the header row or the output grain is unclear in a way that changes the code, ask up to three specific questions and stop instead of guessing.
2. Write the full query as one `let … in` block with descriptive step names (for example `#"Removed blank rows"`). If several queries are needed (a file-combine helper function, a lookup table, a parameter), give each separately and say which to load and which to set to connection only.
3. Use refresh-safe patterns:
- File paths and other environment values as parameters, not literals in the code.
- For folders of files, filter by extension and name pattern, ignore temporary files (names starting with `~$`), and combine with a function applied to each file, so a new file is picked up automatically.
- Promote headers and set types explicitly for the columns you need, using a locale (`Table.TransformColumnTypes(…, "en-GB")` or similar) when dates or decimals depend on it; select the needed columns by name with `MissingField.UseNull` or `MissingField.Ignore` where a missing column should not break the refresh.
- Reshape with `Table.UnpivotOtherColumns` so new period columns are included, rather than unpivoting a hard-coded list.
- Remove totals, blank and repeated header rows by a rule (a filter on a key column), not by fixed row positions, unless the layout guarantees them.
- Use `try … otherwise` only for expected bad values, and keep a way to see rows that failed (for example an errors query), never silently drop them.
- Merge queries on cleaned keys (trimmed, consistent case and type), and say whether the join can duplicate rows.
4. Explain each step in one plain sentence: what it does and why.
5. Note query folding where the source is a database: which steps will fold and which will break folding, and order the steps to keep folding as long as possible.
6. Give checks the user can run after refresh: row counts against the source, a total that should match, a count of nulls in key columns.
</task>
<constraints>
- Write valid M. Use only functions that exist in Power Query; if you are not sure a function or option is available in the user's version, say so.
- Do not invent column names or sample values. Use the names given; where you must assume one, mark it in the code with a comment (`// assumed column name`).
- Comment non-obvious steps inside the code with `//` comments.
- Mention privacy levels if the query combines sources of different kinds (for example a file and a web source), because they can block refresh.
- Keep it as simple as the job allows; prefer UI-reproducible steps where possible so the user can still maintain the query in the editor.
</constraints>
<output_format>
## Plan
Source → steps → output grain, in three to six lines.
## Query
One fenced code block per query, each with its name and whether it loads.
## Step by step
A numbered list: step name — what it does and why.
## Refresh safety
What happens when a new file, a new column, an empty month or a renamed column arrives, and how the query handles it.
## Checks
Checks to run after the first refresh.
## Questions
Only if anything is still assumed.
</output_format>可替换内容
- {{source_description}}
- {{desired_output}}
分类与标签
编程开发、数据清洗、查询脚本、文件夹合并、自动刷新、表格转换