Power Query Transformation Steps Audit for Data Quality
Review the Power Query steps behind a Power BI dataset for risky type changes, silent row loss and hard-coded values, with a verification plan.
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.
Please audit the data preparation steps in Power Query for one query in my Power BI report, to find steps that could silently lose or alter data. Mark all statements about Power Query features and the M language [VERIFY]. Inputs: the list of applied steps in order with a short note on each, or the M code pasted [QUERY_STEPS], row counts before and after major steps if I know them [ROW_COUNTS], the source type and its quirks [SOURCE_DETAILS], how often the source changes shape [SOURCE_CHANGE_FREQUENCY], and any figures that did not match the source [MISMATCHES]. Return: (1) a step table with columns Step number, Step in words, Can it drop or change rows or values?, Risk (Low / Medium / High), Why, Check to run; (2) a list of high-risk patterns: filtering out blanks or errors, changing types with locale issues, replacing errors, removing duplicates on the wrong columns, merging with the wrong join type, hard-coded column names or dates, steps that break when columns move; (3) a reconciliation plan: row count at each stage, sums of a key numeric column, and distinct counts, with a worked example showing the arithmetic; (4) a "resilience" review for column renames and new columns in the source; (5) a documentation convention for naming steps; (6) a cleaned step order proposal, where order matters, as a suggestion. Guardrails: do not rewrite the M code unless I ask; label drafts for testing. Do not assume the intent of a step; ask. Do not publish changes without comparing against source. Add a worked example with invented figures: source has 10,000 rows; after removing errors there are 9,870. Show the 130 lost rows, 1.3 percent, and the questions to answer about them.
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.
See an example result (fictional data)
Step table excerpt (invented) Step 4: Removed rows where Amount is error. Can drop rows: yes. Risk: High. Why: errors may be genuine records with text such as "N/A" or a locale decimal problem. Check: list the removed rows before accepting. Step 7: Changed type of Date to date using the default locale. Risk: Medium. Dates such as 03/04/2024 may be read in the wrong order. Check: compare 10 dates with the source. Reconciliation: source 10,000 rows. After step 4: 9,870. Lost 130 rows (130 / 10,000 = 1.3 percent). Sum of Amount in source column 1,254,300; in output 1,229,800; difference 24,500, which should equal the sum of the lost rows if values are readable. Question: are the 130 rows genuine sales with bad formatting?
Illustrative only, made-up data. Your results will differ and must be reviewed.