Table of contents
At a glance
How do you validate Power Query for a GxP process?
Define the intended processing, including source, data types, joins and output. Test critical transformations against independent expected results, boundary cases and failures. Control the query version, permissions, refresh and error handling in operation. Validate the process that uses Power Query, not the entire product in the abstract.
When does Power Query fall within the scope of validation?
Power Query is a tool for loading and transforming data, such as in Excel or Power BI. For GxP evaluation, it is important to determine its function within the process: Is the batch list used solely for internal planning, or does the evaluation determine whether results are complete? The same technical import can have different implications.
This guide addresses an Excel workbooks with predefined queries. For Power BI, Dataflows, or other execution environments, deployment, access credentials, and update controls may vary. Therefore, transfer the inspection concept, but do not adopt an unverified Excel-specific test script. Document the application, version, data source, and used connector.
For GMP-related applications, Annex 11 provides the framework for risk-based validation and data controls. The following evaluation steps represent our methodological derivation. Neither a specific number of test cases nor a fixed GAMP category arises solely from the name Power Query.
Define the business rule before building the query
Start with a clear processing procedure. Example: Each read measurement possesses a unique measurement ID and a batch reference. Only valid measurements are processed. The absence of a limit value must not result in the measurement disappearing unnoticed or being deemed acceptable without review. From this, intended results can be derived prior to any review of the query code.
Afterward, document each relevant processing step: column selection, data type conversion, filtering, merging, grouping, calculation, and output loading. Record which information is intentionally excluded and the rationale for such exclusions. "Removing duplicates" is not a sufficient technical rule: which fields define a duplicate, and what different records could be lost in the process?
The technical M-script and the list of applied steps are part of the controlled version. A screenshot of the final table shows the result, but not the actual logic used. Additionally, document whether intermediate results, error lists, and excluded data sets are required for potential future reconstruction.
Define data types and geographical schema selectively
Microsoft describes data types and geographic schema as factors influencing the conversion process. For unstructured sources, an automatically detected column does not constitute a professionally validated definition. For example, a batch number "00124" must be retained as an identifier. If it is converted to the number 124, a subsequent linkage may acquire a different meaning.
Define the source format for date and decimal values. "04/05/2026" is ambiguous without a defined format. An import using a German date schema may produce different results than one using American date settings. Therefore, verify both an intentionally ambiguous value and a clearly unambiguous control value such as "25/05/2026" together.
Distinguish a null value from an empty string, whitespace and the number zero. These states can have different meanings in the process. Substituting zero must not conceal a missing measurement. Define units and rounding as well. Even a correctly converted number can give the wrong result if milligrams and grams are confused.
Sources: [2]
Example with independent target result
A query links measurements to a limit table. Five input rows contain the IDs M01 to M05. M01 has a measurement of 9.8 mg with an upper limit of 10.0 mg. M02 has a measurement of 10.2 mg under the same upper limit. M03 has no measurement value. For M04, the limit value is missing. M05 has an exact value of 10.0 mg, falling on the included upper limit. The technical rule requires five clearly traceable result rows.
The independently defined expected outcome is as follows: M01 is deemed valid, M02 is deemed invalid, M03 is deferred as incomplete, and M04 is deferred due to missing specifications; M05 is considered valid as an included edge case. The boundary is evaluated inclusive of the boundary condition. An inner join can remove M04 from the output. A non-critical error substitution could convert M03 into a seemingly valid value. Both actions would compromise the technical judgment.
Develop the expected result from the agreed-upon rule, for example through a separate, controlled manual calculation. Do not copy the results of the same query. Compare IDs, counts, values, units, and decision status. An identical total sum alone cannot detect swapped batches or compensating individual deviations.
| Entrance | Expected Result | Testing objective |
|---|---|---|
| M01 · 9.8 mg · Limit 10.0 mg | Passed; ID and unit received | Normal case |
| M02 · 10.2 mg · Limit 10.0 mg | Failed | Decision on limit value |
| M03 · Measurement value is missing | Incomplete; no determination of existence | Do not replace a zero value with null. |
| M04 · Limit value missing | Deferred. The row remains traceable. | Detect the missing link |
| M05 · exactly 10.0 mg | Passed according to the agreed-upon inclusive rule | Limit and Rounding |
Joins, Filter, and Multi-hit Checks Verify
When combining data, the type of linkage, key fields, and data types influence the outcome. Check a data record without a corresponding counterpart and a key with two matches in the reference table. Such a multi-matching assignment can duplicate a measurement. The resulting dataset will appear complete, but will contain more records than the input.
Define a traceable quantity balance for each stage. In the example, five measurements are read, five unique IDs are generated, and two rows (M03 and M04) are marked for clarification. If rows are deliberately excluded, the balance must explicitly identify this group. Add a test case including leading zeros, whitespaces, and different spellings of the key.
During folder imports, file selection also belongs to the logic. An old export file in the same directory may result in duplicate data delivery. A hidden auxiliary file or a new column name can alter the behavior. Verify the actual selection rule and the system's response to an additional file, not just a cleanly prepared sample folder.
Identify and address errors and updates visibly
An error handling should make technically unexplained data visible. The general removal of all error lines can make an apparently correct table from an incomplete evaluation. Determine when the entire processing is canceled and when a partial result with clear limitation is allowed. This decision depends on the later use.
Assess a missing source access, altered column headers, a non-convertible value, and a partially completed update operation. Also monitor the previous output: if an old table state remains visible, it must not be treated as a newly calculated result. A freely editable date field alone is not a reliable basis for such a determination.
A second update run with unchanged inputs should generate the agreed-upon identical result. When intentionally modified inputs are provided, the change must be visibly reflected at the correct location. Document how the team verifies the update status, incoming data status, and any encountered errors prior to use.
| Disruption | Agreed expected response | Evidence |
|---|---|---|
| Source not reachable | No unverified new release based on outdated data | Status, error message, handling of preliminary values |
| Required column renamed | Structural deviation is detected | Draft version and actual response |
| Reference key duplicated | No undetected duplicate issue | Reconciliation of identifiers and list of unresolved cases |
| Import error message | Errors remain professionally assigned. | Line ID and Root Cause |
| Update interrupted | An incomplete record cannot be used as a completed one | Termination and Retry |
What pertains to release and ongoing operation
The proof package combines purpose, query status, source agreement, risk analysis, target data set, test results and findings. Keep test entries and actual outputs in such a way that another examiner can understand the statement. Document used access data only in suitable protected procedures; passwords do not belong in test protocols.
Changes to query, connector, source schema, or reference data require an impact assessment. Not every change necessitates the same level of testing. Introducing an unused additional column is different from adding a new key or modifying a rounding rule. Justify the extent of repetition based on the affected processing steps.
Designate a responsible party for technical rules and a point of contact for technical operation. In case of errors, the team must know which results are attributable and who evaluates corrections. A clear work instruction including a specific example of a fault is more helpful than a general description of each menu item.
Project experience and collaboration
Test how a transformation handles unknown and missing mappings
Before using Power Query, we establish the expected output independently with the process owners. The reference dataset includes valid values, a missing measurement, an unknown mapping and an unambiguous boundary case. Focused comparisons may suffice for a simple rule. Multiple joins, folder imports or automatic error replacement need additional negative cases. We trace quantities, keys and decision status through to the output. A matching total does not establish that each individual record was assigned correctly.
Frequently asked questions about Power Query validation
Must every Power Query query be validated?
Assess their intended use and the impact on GxP-related processes or records. A purely planning aid versus a batch-specific decision basis carries different risk profiles. For relevant use, the validation evidence must cover the entire required data flow, including source and output. The mere technical existence of a query alone does not determine the scope.
Sources: [1]
Is a comparison of the sums before and after import sufficient?
No, a sum can still match despite swapped IDs or missing and duplicate rows. Additionally, verify unique keys, dataset count, critical individual values, units, and excluded rows. Justify which checks are performed fully and which are conducted on a sample basis based on structure, risks, and identifiable error patterns.
Can outlier values be automatically replaced with zero?
Only when this treatment is professionally justified and thoroughly evaluated. For a missing measurement value, assigning zero is frequently an incorrect assumption. In such cases, the processing should flag the dataset as incomplete or terminate the process. Microsoft describes technical error substitution; this does not imply authorization to interpret unknown values professionally.
Sources: [5]
Is Power Query always a specific GAMP category?
The product name does not define the assessment. Consider the used configuration, custom M-script, modifications, and intended use. Classification serves as a planning aid only and neither replaces understanding of the transformation nor provides evidence of critical functions.
Sources: [1]
How does one assess a change to the data source?
Compare the agreed-upon schema and the affected processing steps. For example, test new columns, modified data types, keys, or units. Then re-verify the technical and subject-matter expected results and relevant error responses. A technically successful refresh confirms only the execution, not the correct interpretation of the data.
Sources: [2]
Can the same validation evidence apply to Excel and Power BI?
Part of the technical validation assessment may be transferable. However, execution environment, connectors, authorizations, and update procedures may differ. Evaluate the identical validated state and supplement environment-specific documentation. Transfer should be based on a justified decision, not a blanket assumption.
Sources: [1]
Sources and review date
As of October 14, 2026. Regulatory classification and manufacturer responsibilities are separately documented. Examples, test matrices, and templates are standalone reference tools; they are customized for actual usage purposes.
- European Commission: EU GMP Annex 11, Computerised Systems (2011)Current version as per EudraLex inventory as of 14.10.2026. Application, risks, operation, and data lifecycle; specific testing proposals are derived independently.
- Microsoft: Data types in Power QueryData types and geographical schema; reviewed on 14.10.2026.
- PIC/S: PI 041-1, Good Practices for Data Management and Integrity (2021)Inspection Guidance on Data Integrity; in particular electronic data, transmission, access, and review.
- Microsoft: Merge queries overviewKey association types and data types.
- Microsoft: Handling errorsFailures at the step and cell level; no GxP compliance confirmation.
Sources checked: .
Test a query against an independently determined result
With work folder, source file, and business rules, we investigate linkages, filters, and error handling. The expected result is determined independently of the same query.
- Computer system validation →
Bring your GxP systems into validated operation, from laboratory software, MES and eQMS to ERP. We define the scope, plan risk-based tests using GAMP 5 and hand the evidence to QA for release.

