Clean and Reshape Excel Data with Power Query Unpivot and Refresh
Clean and Reshape Excel Data with Power Query Unpivot and Refresh is a practical guide to solving a repeatable Office automation problem. A branch report contains many monthly columns; the task is to remove noise, trim text and turn month columns into a tidy month-and-amount pair. We begin by defining the source and the desired output, walk through the technique in manageable stages, and finish with tests, failure cases and operational handover. The goal is not simply to memorize a function name: the reader should be able to measure the result, explain discrepancies and make later changes without losing control of the workflow.
Problem and learning objective
A branch report contains many monthly columns; the task is to remove noise, trim text and turn month columns into a tidy month-and-amount pair.
Before touching a production workbook or database, create a small, recoverable example. A few normal rows, one missing value, one duplicate and at least one boundary case are often enough to expose mistakes. Write the expected answer before running the tool rather than treating a visually attractive grid or a success message as proof. In business reporting, data quality is part of the solution itself, not an optional task performed after coding.
Separate the problem into three layers: incoming data, the transformation or calculation rule, and an output that someone can use. This distinction helps determine whether a failure came from a source file, an incorrect type assumption, or the reporting layer. At each stage record column names, row counts, date limits and stable identifiers. A small audit summary makes it far easier to explain why next month’s report differs from this month’s.
Prerequisites and assumptions
In desktop Excel, Power Query is a data-transformation layer. Its operations appear as Applied Steps and are replayed on refresh, which is different from a one-time manual edit. Remove irrelevant fields early when source files are large so that preview and reconciliation remain manageable. Avoid modifying the raw source when the same task can be expressed as a repeatable transformation with a documented output table.
Tutorial datasets are deliberately compact, so avoid copying examples directly into live processes without reviewing assumptions. Operational data includes locked files, renamed columns, numeric values stored as text and identifiers that unexpectedly repeat. Decide ahead of time whether each case should be rejected, stop the whole job or produce an actionable warning. Each policy can be appropriate, but a silent and unexamined outcome rarely is.
Consider maintenance after the first successful run. A solution may fail later because someone renames a worksheet, adds a source column or upgrades Office. State the essential assumptions at the top of the exercise and near the code that relies on them. When an assumption is violated, a clear error and controlled stop are safer than silently exporting an incomplete report. Good documentation often outlasts clever code tricks.
Step-by-step implementation
A useful automation is repeatable. Given the same source and settings, another run should produce an explainable result instead of depending on which cell was selected or which file happened to be open. Keep constants in one place, document the sequence and make dependencies on the current date or local file path explicit. The less hidden state a procedure uses, the easier it is to debug and hand over to a colleague.
Keep a reproducible raw source
At this stage: Keep the imported source untouched for auditability; use one input Table and a separate Query output rather than overwriting raw cells. Apply this to a small test case and compare the outcome with its input before moving to the next step.
Test “Keep a reproducible raw source” with one normal input and one boundary case. Record the column names, data types and observed result together. If this checkpoint fails, correct its assumption rather than suppressing the error.
Remove repeated headers and empty rows
At this stage: Identify repeated header and grand-total rows before removal. A broad filter can accidentally discard legitimate records with similar labels. Apply this to a small test case and compare the outcome with its input before moving to the next step.
Test “Remove repeated headers and empty rows” with one normal input and one boundary case. Record the column names, data types and observed result together. If this checkpoint fails, correct its assumption rather than suppressing the error.
Assign explicit types to identifier columns
At this stage: Treat branch identifiers as text when leading zeroes are meaningful; do not automatically convert every number-shaped value to numeric type. Apply this to a small test case and compare the outcome with its input before moving to the next step.
Test “Assign explicit types to identifier columns” with one normal input and one boundary case. Record the column names, data types and observed result together. If this checkpoint fails, correct its assumption rather than suppressing the error.
Keep identifier fields selected
At this stage: Mark branch and identifier fields as columns to preserve. This ensures Unpivot rotates only the monthly value columns into rows. Apply this to a small test case and compare the outcome with its input before moving to the next step.
Test “Keep identifier fields selected” with one normal input and one boundary case. Record the column names, data types and observed result together. If this checkpoint fails, correct its assumption rather than suppressing the error.
Unpivot the monthly columns
At this stage: Use Unpivot Other Columns to create Attribute and Value pairs, then rename them Month and Amount for a tidy result. Apply this to a small test case and compare the outcome with its input before moving to the next step.
Test “Unpivot the monthly columns” with one normal input and one boundary case. Record the column names, data types and observed result together. If this checkpoint fails, correct its assumption rather than suppressing the error.
Validate the shaped output after refresh
At this stage: After refresh, compare the number of months and monetary totals per branch with the wide original table. Apply this to a small test case and compare the outcome with its input before moving to the next step.
Test “Validate the shaped output after refresh” with one normal input and one boundary case. Record the column names, data types and observed result together. If this checkpoint fails, correct its assumption rather than suppressing the error.
Working example and code review
The following example demonstrates the structure of clean data in Power Query in a form that can be adapted to a sample workbook or database. Replace the sample table and file names with those in your test environment. Put VBA in a standard module inside a macro-enabled workbook, M code in Power Query Advanced Editor, and spreadsheet expressions in an appropriate worksheet cell.
let
Source = Excel.CurrentWorkbook(){[Name="MonthlySales"]}[Content],
Types = Table.TransformColumnTypes(Source, {{"Branch", type text}}),
Clean = Table.TransformColumns(Types, {{"Branch", each Text.Trim(_), type text}}),
Unpivoted = Table.UnpivotOtherColumns(Clean, {"Branch"}, "Month", "Amount"),
Numeric = Table.TransformColumnTypes(Unpivoted, {{"Amount", type number}}),
Result = Table.SelectRows(Numeric, each [Amount] <> null)
in ResultAfter running the example, reconcile source rows with the output. A branch report contains many monthly columns; the task is to remove noise, trim text and turn month columns into a tidy month-and-amount pair. When multiple operations are involved, validate each one separately: the success of a later step does not prove an earlier transformation was correct. For real customer or finance records, keep an auditable error trail and avoid printing confidential data into broad-access logs or the VBA Immediate Window.
Validation and edge-case tests
Correctness comes before speed. A fast but inaccurate financial summary is worse than a slower report whose totals can be independently reconciled. Compare row counts and control totals with the source and manually inspect some included and excluded records. Repeat those same checks whenever a formula, query or table structure changes. This lightweight regression test is often more valuable than adding another layer of clever automation.
Finish every exercise with a measurable acceptance criterion, not merely a screenshot. The number of processed files should match the eligible input count; a control total should remain consistent; or output identifiers should match the reference table. Explain the result to someone who did not write the automation. If that explanation is difficult, an important assumption or business rule probably remains implicit and deserves another review.
The M language is sensitive to step order and column types. A number-looking string is not necessarily a numeric value, and regional date strings may be parsed with the wrong culture. Apply type conversions after the appropriate text cleanup and at a point where errors are visible. Measure how many rows fail conversion; blindly removing error rows can erase valid finance data and conceal a broken upstream export.
Common pitfalls
| Symptom or risk | Check and correction |
|---|---|
| An unexpected input structure or result at checkpoint 1; confirm field names, types and output cardinality. | Before repeating the full workflow, inspect the test input, record the relevant values and rerun stage 1 independently. Suppressing an error before finding the cause risks an incomplete result. |
| An unexpected input structure or result at checkpoint 2; confirm field names, types and output cardinality. | Before repeating the full workflow, inspect the test input, record the relevant values and rerun stage 2 independently. Suppressing an error before finding the cause risks an incomplete result. |
| A source field has the wrong data type for the operation. | Inspect the column type and a sample value first. |
| New records changed totals or result shape unexpectedly. | Recompute row counts and reconciliation totals. |
Keep a testing workflow separate from the production workflow. Consistent names, recoverable backups and a simple change log prevent many avoidable failures. For finance or personnel data, do not publish real personal records as sample content. If a process produces a downloadable or transferable document, inspect its filename, scope, format and destination before delivery. Automation reduces repetitive effort but does not eliminate responsibility for the results.
Best practices and maintainability
Power Query does not automatically rerun every time a worksheet cell changes. Refresh must occur, and data-source permissions or load settings may affect the result. Moving a workbook can break an absolute folder path. Before handing the report to another user, record its source path, refresh instructions and a recovery procedure, then test with a newly added file rather than relying only on existing samples.
Consider maintenance after the first successful run. A solution may fail later because someone renames a worksheet, adds a source column or upgrades Office. State the essential assumptions at the top of the exercise and near the code that relies on them. When an assumption is violated, a clear error and controlled stop are safer than silently exporting an incomplete report. Good documentation often outlasts clever code tricks.
Correctness comes before speed. A fast but inaccurate financial summary is worse than a slower report whose totals can be independently reconciled. Compare row counts and control totals with the source and manually inspect some included and excluded records. Repeat those same checks whenever a formula, query or table structure changes. This lightweight regression test is often more valuable than adding another layer of clever automation.
Practice exercise
Create a compact practice version of “Clean and Reshape Excel Data with Power Query Unpivot and Refresh”. Add three new inputs: one valid row, one duplicate and one row with a missing value. Write down the expected outcome for each before running the workflow. Compare actual and predicted results side by side, then deliberately rename a column or change a file path to observe failure handling. Finally document how to restore a correct run without deleting useful diagnostic information.
A useful automation is repeatable. Given the same source and settings, another run should produce an explainable result instead of depending on which cell was selected or which file happened to be open. Keep constants in one place, document the sequence and make dependencies on the current date or local file path explicit. The less hidden state a procedure uses, the easier it is to debug and hand over to a colleague.
Finish every exercise with a measurable acceptance criterion, not merely a screenshot. The number of processed files should match the eligible input count; a control total should remain consistent; or output identifiers should match the reference table. Explain the result to someone who did not write the automation. If that explanation is difficult, an important assumption or business rule probably remains implicit and deserves another review.
Related topics
Use the related lessons below to develop a connected learning path. Planned links can be activated once their destination articles are published.
- Access Data from Another Excel File with VBA
- Combine Multiple Excel Files from a Folder with Power Query
- Excel XLOOKUP Tutorial: Exact Matches and Missing Values
- Excel FILTER, UNIQUE and SORT: Dynamic Array Examples
Frequently asked questions
clean data in Power Query: What problem does this technique solve?
This method is useful when its input and intended output can be stated precisely. A branch report contains many monthly columns; the task is to remove noise, trim text and turn month columns into a tidy month-and-amount pair. Start by checking “Keep a reproducible raw source”, then compare a valid record with an invalid or missing-data case. A mismatch does not automatically require rewriting the whole solution; rerun the checkpoint where the result first diverges. This disciplined approach is easier to teach, audit and maintain than a sequence of unexplained clicks.
What should be checked before using it?
This method is useful when its input and intended output can be stated precisely. A branch report contains many monthly columns; the task is to remove noise, trim text and turn month columns into a tidy month-and-amount pair. Start by checking “Remove repeated headers and empty rows”, then compare a valid record with an invalid or missing-data case. A mismatch does not automatically require rewriting the whole solution; rerun the checkpoint where the result first diverges. This disciplined approach is easier to teach, audit and maintain than a sequence of unexplained clicks.
What happens when source data is incomplete?
This method is useful when its input and intended output can be stated precisely. A branch report contains many monthly columns; the task is to remove noise, trim text and turn month columns into a tidy month-and-amount pair. Start by checking “Assign explicit types to identifier columns”, then compare a valid record with an invalid or missing-data case. A mismatch does not automatically require rewriting the whole solution; rerun the checkpoint where the result first diverges. This disciplined approach is easier to teach, audit and maintain than a sequence of unexplained clicks.
How can the result be verified?
This method is useful when its input and intended output can be stated precisely. A branch report contains many monthly columns; the task is to remove noise, trim text and turn month columns into a tidy month-and-amount pair. Start by checking “Keep identifier fields selected”, then compare a valid record with an invalid or missing-data case. A mismatch does not automatically require rewriting the whole solution; rerun the checkpoint where the result first diverges. This disciplined approach is easier to teach, audit and maintain than a sequence of unexplained clicks.
Is this suitable for production reporting?
This method is useful when its input and intended output can be stated precisely. A branch report contains many monthly columns; the task is to remove noise, trim text and turn month columns into a tidy month-and-amount pair. Start by checking “Unpivot the monthly columns”, then compare a valid record with an invalid or missing-data case. A mismatch does not automatically require rewriting the whole solution; rerun the checkpoint where the result first diverges. This disciplined approach is easier to teach, audit and maintain than a sequence of unexplained clicks.
Conclusion
clean data in Power Query becomes genuinely useful when its result is repeatable and independently checkable. A branch report contains many monthly columns; the task is to remove noise, trim text and turn month columns into a tidy month-and-amount pair. By working in small stages, testing edge cases and recording assumptions, a short example can become a reliable workflow. Before processing live records, repeat the checks on a backup and confirm compatibility with the relevant Office version and data model.