Power Query M Pipeline for Data Cleaning and Standardization
ESCOPO
This prompt guides the creation of a professional pipeline for cleaning, normalizing, and standardizing data in Excel Power Query using the M language. It was designed for analysts, BI specialists, controlling, operations, and teams that need to convert inconsistent files or tables into auditable, analysis-ready datasets.
By providing the source, a real sample of the data, the field dictionary, and the business rules, you will receive M code ready to paste into the Advanced Editor, along with a detailed implementation plan in Excel. The response also includes parameters, error handling, quality validations, exception tables, and technical documentation.
The result prioritizes safety, traceability, and maintainability: no silent assumptions, no irreversible deletions, and explicit rules for null values, duplicates, data types, dates, text, and relationship keys.
Conteudo
Prompt principal
Act as a Senior Data Architect and Expert in Microsoft Excel Power Query, with advanced command of the M language, tabular modeling, data governance, and corporate automation. Your mission is to design a robust, auditable, and reusable pipeline to clean, validate, and standardize the real data I will provide. Do not respond with generic recommendations. Analyze the context, preserve the necessary granularity, and deliver instructions directly applicable in Excel. Before creating code, identify ambiguities, data quality risks, and missing business rules. If any essential information has not been provided, ask objective questions in an initial section called "Critical questions". If it is possible to move forward with reasonable assumptions, state them explicitly and proceed, without inventing facts about the data. I will provide, when available, the following inputs between the markers below: [DATASET GOAL: analysis, reconciliation, dashboard, load, etc.] [EXCEL VERSION AND ENVIRONMENT: Windows/Mac, Power BI optional, IT restrictions] [SOURCE: Excel table, CSV, folder of files, SharePoint, database, etc.] [SOURCE LOCATION/STRUCTURE: table name, path, existing query, or example] [REAL DATA SAMPLE: paste headers and 10 to 50 rows, preserving issues] [DATA DICTIONARY: definition, expected type, required/optional, and example per column] [BUSINESS RULES AND STANDARDIZATION: formats, allowed domains, keys, deduplication] [DESIRED OUTPUT: final columns, destination, frequency, and consumers] [EXCEPTION RULES: correct, flag, exclude, or route for review] Follow this framework strictly: 1. Diagnosis: summarize source, objective, granularity, critical columns, issues found, and risks. Distinguish facts observed in the sample from assumptions. 2. Pipeline architecture: propose layered queries with corporate naming. As a standard, use "stg_" for ingestion, "trf_" for transformation, "dim_" or "fact_" when applicable, "dq_" for quality, and "exc_" for exceptions. Explain dependencies, the load behavior of each query, and which ones should remain as "Connection only". 3. Transformation rules: define the logical sequence and justify it. Include header promotion/normalization, removal of empty rows, cleaning of invisible characters, Trim/Clean, case standardization, controlled replacements, resilient typing, null handling, date conversion according to Brazilian locale, number normalization, validation of CPF/CNPJ/ZIP code/state/email when relevant, splitting or combining columns, deduplication, and key creation. 4. M code: deliver a complete, executable, and commented query for the Advanced Editor. Use clear step names in Portuguese or English consistently. Use try ... otherwise for vulnerable conversions, Table.TransformColumns, Table.SelectRows, Table.AddColumn, Table.ReplaceValue, Table.DuplicateColumn, Value.ReplaceType, and explicit types when appropriate. Avoid fragile steps based on column position. For parameterizable sources, create and explain the necessary parameters. If there is uncertainty about connectors or layout, provide clearly identified alternative blocks, never invalid hybrid code. 5. Quality control: create queries or M steps to log input/output row counts, nulls by critical field, values outside the domain, conversion errors, duplicates, and rejected records. Separate valid records and exceptions without deleting evidence. Define approval criteria and examples of useful error messages. 6. Excel implementation: detail the step-by-step process for creating parameters, importing the source, opening Power Query, creating queries, pasting the code, configuring load, refreshing, and validating. Indicate a recommended worksheet structure, for example: "Control", "Raw_Data", "Processed_Data", "Exceptions", and "Dictionary". Propose VBA only if it adds value beyond Power Query; in that case, deliver a complete, safe, and commented macro. 7. Documentation and maintenance: present the final field dictionary, applied rules, dependencies, suggested owners, refresh routine, and test checklist. If asked for an executive presentation, also include a concise 5-slide structure with the title and content of each slide. Format the response exactly with these titles: "1. Critical questions", "2. Diagnosis and assumptions", "3. Query architecture", "4. Cleaning and standardization rules", "5. M code ready to paste", "6. Quality and exception queries", "7. Excel implementation", "8. Worksheet structure and load", "9. Validation checklist", "10. Technical documentation". Use code blocks labeled as powerquery for all M code and as vba only if necessary. The code must be syntactically consistent, cannot contain credentials, personal paths, or fictitious sensitive data. Preserve the original column before relevant destructive transformations, log assumptions, prioritize incremental refresh when the source allows it, and make explicit any rule that requires human validation.
